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

Откуда:
Сообщений: 7711
create table TablePrimary (Id number, Val varchar(255));

insert into TablePrimary (Id, Val) values (1, 'p1');
insert into TablePrimary (Id, Val) values (2, 'p2');
insert into TablePrimary (Id, Val) values (3, 'p3');
insert into TablePrimary (Id, Val) values (4, 'p4');
insert into TablePrimary (Id, Val) values (5, 'p5');

create table TableSecondary (Id number, Val varchar(255));

insert into TableSecondary (Id, Val) values (1,'s1');
insert into TableSecondary (Id, Val) values (3,'s3');
insert into TableSecondary (Id, Val) values (5,'s5');

merge into TableSecondary dest
  using TablePrimary src
    on (dest.Id = src.Id)
  when matched then
    update set dest.Val = src.Val
    where (src.Id=3)
  delete where (src.Id<>3);

select * from TableSecondary order by Id;
IdVal
1s1
3p3
5s5

Почему не
IdVal
3p3


Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.

1 && 5 и под условие в on и под delete'вское where - попадают. Почему тогда не удалились? Или я не правильно что-то понял?
_________________
"Helo, word!" - 17 errors 56 warnings
28 май 10, 13:52    [8851720]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
Ex_Soft
Member

Откуда:
Сообщений: 7711
Опа, а
merge into TableSecondary dest
  using TablePrimary src
    on (dest.Id = src.Id)
  when matched then
    update set dest.Val = src.Val
    --where (src.Id=3)
  delete where (src.Id<>3);
как раз и дало ожидаемый
IdVal
3p3

но ни грамма не поспособствовало пониманию...
Я предполагаю что должно происходить следующее:
1. Производиться join по on
2. По всем row полученного от join'a
2.1. Если выполняется where update'а - делается update
2.2. Если выполняется where delete'а - делается delete
Или я ошибаюсь?
28 май 10, 14:04    [8851841]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 5963
Ex_Soft,

не совсем так
The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.
28 май 10, 14:06    [8851853]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
Ex_Soft
Member

Откуда:
Сообщений: 7711
env
The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.

Гм... Удаляются только те записи, которые про'update'лись? Но тогда зачем делать "лишний" update? Да и на пальцах:
1. Производиться join по on
2. По всем row полученного от join'a
2.1. Если выполняется where update'а - делается update
2.2. Если выполняется where update'а (т.е. запись про'update'лась) && where delete'а - делается delete
Но where update'а и where delete'а, как правило, взаимоисключающие:
http://www.oracle-wiki.ru/wiki/Merge

MERGE INTO destination_table dest
  USING (SELECT col1, col2, col3 FROM source_table) source1
      ON (dest.col1 = source1.col1)
      WHEN MATCHED THEN
          UPDATE SET dest.col2 = source1.col2,
                                   dest.col3 = source1.col3
          WHERE source1.col2 IS NOT NULL
          DELETE source1.col2 IS NULL
      WHEN NOT MATCHED THEN
           INSERT (dest.col1, dest.col2, dest.col3)
           VALUES (source1.col1, source1.col2, source1.col3)
           WHERE source1.col2 IS NOT NULL

Получается delete вообще не произойдет. Что и имеет место в первом примере.
IMHO, довольно-таки довольно неожиданное и нетривиальное поведение...
28 май 10, 14:37    [8852127]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 5963
Ex_Soft,

автор
Удаляются только те записи, которые про'update'лись? Но тогда зачем делать "лишний" update?


емнип, delete проходит по значениям получившимся после update. Т.е. одно из применений - помечаем записи как старые и сразу удаляем.
28 май 10, 14:42    [8852158]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
-2-
Member

Откуда:
Сообщений: 14994
Ex_Soft
Но where update'а и where delete'а, как правило, взаимоисключающие:
http://www.oracle-wiki.ru/wiki/Merge

MERGE INTO destination_table dest
  USING (SELECT col1, col2, col3 FROM source_table) source1
      ON (dest.col1 = source1.col1)
      WHEN MATCHED THEN
          UPDATE SET dest.col2 = source1.col2,
                                   dest.col3 = source1.col3
          WHERE source1.col2 IS NOT NULL
          DELETE source1.col2 IS NULL
      WHEN NOT MATCHED THEN
           INSERT (dest.col1, dest.col2, dest.col3)
           VALUES (source1.col1, source1.col2, source1.col3)
           WHERE source1.col2 IS NOT NULL

Получается delete вообще не произойдет.
Опять ищем интерпретации, при том, что в документации ВСЕ поясняет один абзац. Вот вам следующе предложение:
MERGE
The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition.
28 май 10, 14:43    [8852161]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
Ex_Soft
Member

Откуда:
Сообщений: 7711
-2-
MERGE
The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition.

И что Вы этим хотите сказать?
merge into TableSecondary dest
  using TablePrimary src
    on (dest.Id = src.Id)
  when matched then
    update set dest.Val = src.Val
    where (src.Id=3)
  delete where (src.Id<>3);
здесь вообще в where delete'а src - TablePrimary, а модифицируется dest - TableSecondary
28 май 10, 16:13    [8852997]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 5963
Ex_Soft,

Не понял вашего удивления.

Всё правильно, только строки попавшие под условия update прошли на вход delete.
28 май 10, 16:27    [8853103]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15454
Ex_Soft
..Гм... Удаляются только те записи, которые про'update'лись? Но тогда зачем делать "лишний" update?..
IMHO, довольно-таки довольно неожиданное и нетривиальное поведение...

всё понятно и ожидаемо, если на сабж посмотреть в его развитии.
9i - в мерже дилитовой кляузы просто не было и, соответственно,
where в апдейтовой кляузе имел смысл ограничения всея when matched.
ну а когда дилит добавили, то добавили, как добавили.
можно считать что удаляются именно проапдейченные строки - мнемонически, наверное,
так понятнее осознать (и запомнить) сей камуфлет.
а взаправду ли апедейчутся ли они перед удалением, думаю, что нет.., ну а какая разница?
28 май 10, 16:30    [8853141]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
Ex_Soft
Member

Откуда:
Сообщений: 7711
env
Не понял вашего удивления.

Всё правильно, только строки попавшие под условия update прошли на вход delete.

http://www.oracle-wiki.ru/wiki/Merge

MERGE INTO destination_table dest
  USING (SELECT col1, col2, col3 FROM source_table) source1
      ON (dest.col1 = source1.col1)
      WHEN MATCHED THEN
          UPDATE SET dest.col2 = source1.col2,
                                   dest.col3 = source1.col3
          WHERE source1.col2 IS NOT NULL
          DELETE source1.col2 IS NULL
      WHEN NOT MATCHED THEN
           INSERT (dest.col1, dest.col2, dest.col3)
           VALUES (source1.col1, source1.col2, source1.col3)
           WHERE source1.col2 IS NOT NULL

На вход к update попадут записи у коих source1.col2 IS NOT NULL, далее они попадают на вход delete и проверяются на source1.col2 IS NULL. Ессесно такого не может быть, так как иначе они бы не попали под update. Два взаимоисключающих условия. delete, вопреки ожиданиям, - никогда не произойдет. Просто сам синтаксис, IMHO, не способствует интуитивно понятному трактованию сей конструкции.
orawish
всё понятно и ожидаемо, если на сабж посмотреть в его развитии.
9i

Да я тока-тока намедни с оракулом столкнулсо...
orawish
а взаправду ли апедейчутся ли они перед удалением, думаю, что нет.., ну а какая разница?

create or replace trigger tr_AU_TableSecondary
after update
on TableSecondary
referencing new as new
for each row
  begin
    dbms_output.put_line('tr_AU_TableSecondary');
  end;
PL/SQL Developer -> DBMS Output

tr_AU_TableSecondary

Но в trigger'е ж может быть не невинное put_line, а кое-что посерьезнее...
28 май 10, 17:37    [8853647]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15454
Ex_Soft
..
Но в trigger'е ж может быть не невинное put_line, а кое-что посерьезнее...

Что? ~армагедец? а по какому поводу?
в триггере или аудит живёт или реакция на изменение значений атрибутов.
пусть себе и аудитит и реагирует.
28 май 10, 17:47    [8853710]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
-2-
Member

Откуда:
Сообщений: 14994
Ex_Soft
Но в trigger'е ж может быть не невинное put_line, а кое-что посерьезнее...
Может у оракла и не лучшая реализация MERGE, но что-то посерьезнее так или иначе требует навыков чтения документации:
MERGE
If the update clause is executed, then all update triggers defined on the target table are activated.
...
Any delete triggers defined on the target table will be activated for each row deletion.
28 май 10, 17:48    [8853713]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 5963
Ex_Soft,

Вы просто лучше не примеры на oracle-wiki.ru смотрите, а читайте доку от производителя. Там конечно тоже ляпы бывают, но реже.
28 май 10, 17:54    [8853749]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
Ex_Soft
Member

Откуда:
Сообщений: 7711
orawish
в триггере или аудит живёт или реакция на изменение значений атрибутов.
пусть себе и аудитит и реагирует.

А зачем реагировать на изменение атрибутов в row, которая затем будет удалена?
-2-
Может у оракла и не лучшая реализация MERGE, но что-то посерьезнее так или иначе требует навыков чтения документации:
MERGE
If the update clause is executed, then all update triggers defined on the target table are activated.
...
Any delete triggers defined on the target table will be activated for each row deletion.

Это Вы к чему? Что при update trigger сработает - дык мы это уже выяснили...
env
Вы просто лучше не примеры на oracle-wiki.ru смотрите, а читайте доку от производителя

Понято...
28 май 10, 18:15    [8853852]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15454
Ex_Soft
orawish
в триггере или аудит живёт или реакция на изменение значений атрибутов.
пусть себе и аудитит и реагирует.

А зачем реагировать на изменение атрибутов в row, которая затем будет удалена?
..

например, чтобы ~сошлось..
если на восходящую денормализацию посмотреть, то:
insert +5
update -2
delete -3
итого: 0
28 май 10, 18:45    [8854015]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9635
orawish
[quot Ex_Soft
а взаправду ли апедейчутся ли они перед удалением, думаю, что нет.., ну а какая разница?


Triggers.

SY.
28 май 10, 20:01    [8854379]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: merge + delete  [new]
escaper_
Member

Откуда:
Сообщений: 15
Всем доброго дня!

Предлагаю разминку: кто сможет предложить оптимальный способ решения этой задачи через MERGE?

Сам сделал так:

/*
3.	В LOCATIONS планируется сделать POSTAL_CODE обязательным полем. 
Если в ней есть такие офисы, у которых не указан POSTAL_CODE, но он используется в DEPARTMENTS, то поле заполнить как UNKNOWN, 
если не используется, то удалить
*/

MERGE INTO locations l USING (
                                SELECT
                                    ll.location_id   AS loc_id_1,
                                    dd.location_id   AS loc_id_2
                                FROM
                                    locations     ll
                                    LEFT JOIN departments   dd ON ( ll.location_id = dd.location_id )
                            )
x ON ( l.location_id = x.loc_id_1 )
WHEN MATCHED THEN UPDATE SET l.postal_code =
    CASE
        WHEN l.postal_code IS NULL THEN
            'UNKNOWN'
        ELSE
            l.postal_code
    END
DELETE
WHERE
    x.loc_id_2 IS NULL;
29 окт 19, 10:33    [22004810]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29402
escaper_
этой задачи
RTFM
29 окт 19, 10:59    [22004837]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
merch
Member

Откуда:
Сообщений: 156
escaper_, почти сделал.. но "поймал" ошибку ORA-00942.
29 окт 19, 16:14    [22005311]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
escaper_
Member

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

Хотя простой ответ RTFM бывает оправдан, когда дается просто лентяю, ссылка на документацию (даже если это набор ключевых слов для поиска в Google) все же лучше.


Можете послать поближе? :)
7 ноя 19, 08:58    [22011202]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
-2-
Member

Откуда:
Сообщений: 14994
escaper_
через MERGE
Ради зачем?
7 ноя 19, 09:05    [22011207]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
escaper_
Member

Откуда:
Сообщений: 15
Через MERGE потому, что такое условие задачи. А как вы видите решение?

Сообщение было отредактировано: 7 ноя 19, 09:34
7 ноя 19, 09:31    [22011221]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
-2-
Member

Откуда:
Сообщений: 14994
escaper_
Через MERGE потому, что такое условие задачи. А как вы видите решение?
update, delete, фиктивный merge для соблюдения "условие задачи".
7 ноя 19, 09:45    [22011233]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 5963
escaper_,

delete, update или delete, modify not null default 'UNKNOWN'
7 ноя 19, 10:12    [22011256]     Ответить | Цитировать Сообщить модератору
 Re: merge + delete  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17717
escaper_
Сам сделал так:

Готовьтесь отражать ORA-30926
7 ноя 19, 11:35    [22011353]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить