Удалить с левой стороны Присоединяйтесь к Oracle 10g

У меня есть следующий код, который отлично работает в MS SQL Server:

delete grp
from grp
left join my_data
on grp.id1 = my_data.id1
and grp.id2 = my_data.id2
and grp.id3 = my_data.id3
and grp.id4 = my_data.id4
where my_data.id1 is NULL

В принципе, я хочу удалить все вхождения, которые могут быть найдены в grp и не имеют эквивалентности in my_data. К сожалению, он не работает в Oracle 10g. Я попытался использовать старый синтаксис для левого соединения (+), но он тоже не работает. Вот так:

delete grp
from grp,
my_data
where grp.id1 = my_data.id1 (+)
and grp.id2 = my_data.id2 (+)
and grp.id3 = my_data.id3 (+)
and grp.id4 = my_data.id4 (+)
and my_data.id1 is NULL

Предложение

A IN будет работать, если у меня не было нескольких ключей, но я не вижу, как использовать его с моими данными. Итак, какова альтернатива?

Ответы

Ответ 1

Таблицы и данные:

SQL> create table grp (id1 number null, id2 number null, id3 number null, id4 number null);    
Table created.

SQL> create table my_data (id1 number null, id2 number null, id3 number null, id4 number null);

Table created.

SQL> insert into grp values (1, 2, 3, 4);

1 row created.

SQL> insert into grp values (10, 20, 30, 40);

1 row created.

SQL> insert into grp values (1, 2, 30, 40);

1 row created.

SQL> insert into my_data values (1, 2, 3, 4);

1 row created.

SQL> commit;

Commit complete.

Использование in. Примечание Не использовать, если идентификаторы в подзапросе могут быть null. Not in of null никогда не возвращает true.

SQL> delete grp where (id1, id2, id3, id4) not in (select id1, id2, id3, id4 from my_data);

2 rows deleted.

SQL> select * from grp;

       ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
         1          2          3          4

Используя exists

SQL> rollback;

Rollback complete.

SQL> delete grp where not exists (select * from my_data where grp.id1 = my_data.id1 and grp.id2 = my_data.id2 and grp.id3 = my_data.id3 and grp.id4 = my_data.id4);

2 rows deleted.

SQL> select * from grp;

       ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
         1          2          3          4

SQL>

Ответ 2

Решение Shannon - это путь: используйте оператор NOT IN (или NOT EXISTS).

Однако вы можете удалить или обновить соединение в Oracle, но синтаксис не совпадает с MS SQL Server:

SQL> DELETE FROM (SELECT grp.*
  2                  FROM grp
  3                  LEFT JOIN my_data ON grp.id1 = my_data.id1
  4                                   AND grp.id2 = my_data.id2
  5                                   AND grp.id3 = my_data.id3
  6                                   AND grp.id4 = my_data.id4
  7                 WHERE my_data.id1 IS NULL);

2 rows deleted

Кроме того, Oracle только позволит вам обновить соединение, если нет двусмысленности относительно того, к какой базовой строке будет обращаться оператор. В частности, Oracle не будет рисковать обновлением или удалением (утверждение не будет выполнено), если есть вероятность того, что строка может появиться дважды в соединении. В этом случае удаление будет работать только в том случае, если существует ограничение UNIQUE на my_data(id1, id2, id3, id4).

Ответ 3

Если вы хотите убедиться, что нет никакой двусмысленности в том, что нужно удалить, вы можете изменить решение Vincent:

delete from grp where rowid in
    (
    select
         grp.rowid
    from
         grp left outer join my_data on
            grp.id1 = my_data.id1
        and grp.id2 = my_data.id2
        and grp.id3 = my_data.id3
        and grp.id4 = my_data.id4
    where
        my_data.id1 is NULL
    )