Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Добрый день

Есть таблица кодов стран tab1

tab1
code          code_name                              tariff
79             Russia (mob) - region                 0,01
7903          Russia (mob) - Beeline                0,02
790300       Russia, Moscow (mob) - Beeline   0,03

которую нужно обновить таблицей tab2
tab2
code          code_name                              tariff
79             Russia (mob) - region                 0,011
7903          Russia (mob) - Beeline                0,022

таким образом чтобы брался код из tab2 с наименьшим кодом страны (79) и производилось обновление всех подкодов из таблицы tab1 (79, 7903, 790300). Затем брался следующий наименьший код страны tab2 (7903) и производилось обновление всех подкодов tab1 (7903, 790300). То есть отчет должен выглядить так

tab1
code          code_name                              tariff
79             Russia (mob) - region                 0,011
7903          Russia (mob) - Beeline                0,022
790300       Russia, Moscow (mob) - Beeline   0,022

Я хотел использовать следующий запрос с Merge

MERGE INTO tab1 tt
USING (select * from tab2
order by code asc) t 
ON (t.code = substr(tt.code,1,length(t.code)))
WHEN MATCHED THEN
  UPDATE
     SET tt.tariff      = t.tariff
WHEN NOT MATCHED THEN
  INSERT
    (tt.code, tt.code_name, tt.tariff)
  VALUES
    (t.code,
     t.code_name,
     t.tariff,
     );
но выходит ошибка ORA-30926: unable to get a stable set of rows in the source tables. Помогите разобраться плз.
4 мар 10, 08:42    [8427986]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Meder,

З.Ы. Поиском пользовался, увы по моей части ничего не нашел
4 мар 10, 08:45    [8427998]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Elic
Member

Откуда:
Сообщений: 29977
Meder
таким образом чтобы брался код из tab2 с наименьшим кодом страны (79) и производилось обновление всех подкодов из таблицы tab1 (79, 7903, 790300). Затем брался следующий наименьший код страны tab2 (7903) и производилось обновление всех подкодов tab1 (7903, 790300).
Одно строку нельзя изменять более одного раза. Хотя бы потому, что нельзя обеспечить "сперва, а затем следующий" :)
4 мар 10, 09:05    [8428075]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Elic
Meder
таким образом чтобы брался код из tab2 с наименьшим кодом страны (79) и производилось обновление всех подкодов из таблицы tab1 (79, 7903, 790300). Затем брался следующий наименьший код страны tab2 (7903) и производилось обновление всех подкодов tab1 (7903, 790300).
Одно строку нельзя изменять более одного раза. Хотя бы потому, что нельзя обеспечить "сперва, а затем следующий" :)

Каким запросом тогда это реализовать, если не Merge'em?
4 мар 10, 09:12    [8428102]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Elic
Member

Откуда:
Сообщений: 29977
Meder
Каким запросом тогда это реализовать, если не Merge'em?
merge-ем, только добавить lead() в источник +
on (dst like src||'%' and (dst < lead_src or lead_src is null))
4 мар 10, 09:18    [8428118]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
А такая конструкция:
with t1 as (select 79 code, 'r' code_name, 0.01 tariff from dual union all
            select 7903,'r2',0.02 from dual union all
            select 790300,'r3',0.03 from dual),
     t2 as (select 79 code,'R4' code_name, 0.011 tariff from dual union 
            select 7903, 'R5', 0.022 from dual),
     t3 as (select t1.code codet1,max(t2.code) codet2
              from t2 left join t1 on  to_char(t1.code) like to_char(t2.code)||'%'
              group by t1.code)
select * from t3;
не наводит вас на мысль?

Хотя я бы всетаки делал бы через PL/SQL.
4 мар 10, 10:42    [8428824]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Elic
Meder
Каким запросом тогда это реализовать, если не Merge'em?
merge-ем, только добавить lead() в источник +
on (dst like src||'%' and (dst < lead_src or lead_src is null))


Спасибо за подсказку Elic. Использовал следующий запрос

MERGE INTO tab1 tt
USING (select code, lead(code) over (order by code) as lead_code, code_name, tariff from mn_codes_normal t) t 
ON (tt.code like t.code||'%' and (tt.code < t.lead_code or t.lead_code is null))
WHEN MATCHED THEN
  UPDATE
     SET tt.tariff      = t.tariff
WHEN NOT MATCHED THEN
  INSERT
    (tt.code, tt.code_name, tt.tariff)
  VALUES
    (t.code,
     t.code_name,
     t.tariff,
     );

Единственное он не обновляет когда в таблице tab1 будет дополнительно следующая строка


tab1
code          code_name                              tariff
79             Russia (mob) - region                 0,01
7903          Russia (mob) - Beeline                0,02
790300       Russia, Moscow (mob) - Beeline   0,03

7905	   Russia (mob) - Beeline	           0,04


которая после обработки Merge выдаст


tab1
code          code_name                              tariff
79             Russia (mob) - region                 0,011
7903          Russia (mob) - Beeline                0,022
790300       Russia, Moscow (mob) - Beeline   0,022

7905	   Russia (mob) - Beeline	           0,04


что неверно
4 мар 10, 11:51    [8429496]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Elic
Member

Откуда:
Сообщений: 29977
Meder
что неверно
Точно. Значит придётся в using получить точное соответствие кода источника коду назначения, чтобы потом on(tt.code=t.code).
4 мар 10, 11:59    [8429579]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Elic
Meder
что неверно
Точно. Значит придётся в using получить точное соответствие кода источника коду назначения, чтобы потом on(tt.code=t.code).


Не совсем понял. Сможешь пояснить на примере Merge?
4 мар 10, 12:06    [8429651]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Meder
Сможешь пояснить на примере Merge?
обычно ответы пишут select-образные, когда автор позаботится привести входные данные в удобном виде...
4 мар 10, 12:08    [8429675]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
JaRo
обычно ответы пишут select-образные, когда автор позаботится привести входные данные в удобном виде...


create table tab1 as (select 79 code, 'r' code_name, 0.01 tariff from dual union all
            select 7903,'r2',0.02 from dual union all
            select 790300,'r3',0.03 from dual union all
            select 7905,'r4',0.04 from dual);
            
            
create table tab2 as (select 79 code,'R5' code_name, 0.011 tariff from dual union 
            select 7903, 'R6', 0.022 from dual);
            
MERGE INTO tab1 tt
USING (select code, lead(code) over (order by code) as lead_code, code_name, tariff from tab2 t) t 
ON (tt.code like t.code||'%' and (tt.code < t.lead_code or t.lead_code is null))
WHEN MATCHED THEN
  UPDATE
     SET tt.tariff      = t.tariff
WHEN NOT MATCHED THEN
  INSERT
    (tt.code, tt.code_name, tt.tariff)
  VALUES
    (t.code,
     t.code_name,
     t.tariff,
     );
     
select * from tab1;
4 мар 10, 12:23    [8429840]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
JaRo
обычно ответы пишут select-образные, когда автор позаботится привести входные данные в удобном виде...


Пардон, лишняя запятая, исправлено

create table tab1 as (select 79 code, 'r' code_name, 0.01 tariff from dual union all
            select 7903,'r2',0.02 from dual union all
            select 790300,'r3',0.03 from dual union all
            select 7905,'r4',0.04 from dual);
            
            
create table tab2 as (select 79 code,'R5' code_name, 0.011 tariff from dual union 
            select 7903, 'R6', 0.022 from dual);
            
MERGE INTO tab1 tt
USING (select code, lead(code) over (order by code) as lead_code, code_name, tariff from tab2 t) t 
ON (tt.code like t.code||'%' and (tt.code < t.lead_code or t.lead_code is null))
WHEN MATCHED THEN
  UPDATE
     SET tt.tariff      = t.tariff
WHEN NOT MATCHED THEN
  INSERT
    (tt.code, tt.code_name, tt.tariff)
  VALUES
    (t.code,
     t.code_name,
     t.tariff
     );
     
select * from tab1;
4 мар 10, 12:28    [8429902]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
то есть результат должен быть как

tab1
code          code_name                              tariff
79             Russia (mob) - region                 0,011
7903          Russia (mob) - Beeline                0,022
790300       Russia, Moscow (mob) - Beeline   0,022

7905	   Russia (mob) - Beeline	           0,011
4 мар 10, 12:38    [8429997]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Elic
Member

Откуда:
Сообщений: 29977
Meder
Elic
Значит придётся в using получить точное соответствие кода источника коду назначения, чтобы потом on(tt.code=t.code).
Не совсем понял. Сможешь пояснить на примере Merge?
Дежа вю, однако :)
4 мар 10, 12:52    [8430146]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Народ, тема остается актуальной

Elic,

Если ты к тому что еще держишь обиду на меня за прошлое, то извини, я не хотел задеть тебя.
4 мар 10, 14:16    [8430905]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Elic
Member

Откуда:
Сообщений: 29977
Meder
Если ты к тому что еще держишь обиду на меня за прошлое, то извини, я не хотел задеть тебя.
Там пример кода. Т.е. принцип.
А про обиды я не читал
4 мар 10, 14:35    [8431078]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Я не такой продвинутый SQL программист, но я не вижу как применить этот принцип у себя. Я понимаю никто за меня работу делать не будет, но уж очень нужен этот запрос.
5 мар 10, 06:20    [8434448]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Meder,

Если все еще актуально, то

set autotrace off;

create table tab1 (code number(10) primary key,code_name varchar(25 char),tariff number(8,2));

insert into tab1 (select 79 code, 'r' code_name, 0.01 tariff from dual union all
            select 7903,'r2',0.02 from dual union all
            select 790300,'r3',0.03 from dual union all
            select 7905,'r4',0.04 from dual);
            
            
create table tab2 as (select 79 code,'R5' code_name, 0.011 tariff from dual union 
            select 7903, 'R6', 0.022 from dual
            union select 80,'не существует',0.4 from dual);

select * from tab1;

set autotrace on;

declare 
 cursor cr is select rowid r,code from tab1 for update of tab1.tariff;
 rid rowid;
 vcode number;
 vtariff number;
begin
 open cr;
 loop
  fetch cr into rid,vcode;
  exit when cr%notfound;
  select tariff into vtariff from (select tariff from tab2 where to_char(vcode) like to_char(code)||'%' order by code desc) where rownum=1;
  if sql%found then
   update tab1
      set tariff=vtariff
    where rowid=rid;
  end if;
 end loop;
 close cr;
end;
/
insert into tab1 (code,code_name,tariff)
 select code,code_name,tariff
   from tab2
  where tab2.code not in (select code from tab1);

set autotrace off;
select * from tab1;

rollback;

set autotrace on;
merge into tab1 t
 using (select t1.code codet1,max(t2.code) codet2, max(t2.code_name) new_name, max(t2.tariff) new_tariff
              from tab2 t2 left join tab1 t1 on to_char(t1.code) like to_char(t2.code)||'%'
              group by t1.code
        union all select null,code,code_name,tariff from tab2 
         where code not in (select code from t1)) tn
 on (t.code=tn.codet1)
 when matched then
  update set t.tariff=(select max(tariff) from tab2 where code=tn.codet2)
 when not matched then
  insert (t.code,t.code_name,t.tariff)
   values (codet2,new_name,new_tariff);

set autotrace off;
select * from tab1;
12 мар 10, 10:49    [8467017]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Извините вкраласть опечатка

        union all select null,code,code_name,tariff from tab2 
         where code not in (select code from t1)) tn
следует читать как:
        union select null,t3.code,t3.code_name,t3.tariff from tab2 t3
         where t3.code not in (select code from tab1)) tn
12 мар 10, 11:13    [8467260]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Сергей Арсеньев,

Огромное Вам спасибо.
15 апр 10, 09:26    [8632344]     Ответить | Цитировать Сообщить модератору
 Re: Merge обновление всех подкодов  [new]
Meder
Member

Откуда:
Сообщений: 114
Сергей Арсеньев,

Единственная недоработка, это последний запрос Merge, вставляет лишнюю строку-дубликат, по полю Code, значения тарифов и название кода которых неверные.

9989789	Zambia mobile		1,485	ЛОЖЬ
9989789	Uzbekistan mobile 	0,0704	ИСТИНА

Но все равно, огромное Вам спасибо.
15 апр 10, 14:53    [8634764]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить