приJOINить и удалить

Есть две таблицы EMP (схема SCOTT) и EMP_2. Последнюю сделаем путем копирования из EMP и удаление/добавление нескольких записей.
Задача такова: нужно удалить все записи из таблицы EMP_2, которые встречаются в EMP.

Итак, если таблица EMP_2 еще не создана:

CREATE TABLE EMP_2 AS (SELECT * FROM EMP);

Если же таблицу создали, то наполнить ее можно так:

INSERT INTO EMP_2 SELECT * FROM EMP;

Удаляем несколько записей и можно добавить свои уникальные.

Решение 1:

DELETE FROM EMP_2 E2
WHERE E2.EMPNO IN (
SELECT E.EMPNO FROM EMP E
);

Здесь хочу сразу оговориться, что количество операторов во множестве IN не может превышать тысячу с лишним, поэтому этот вариант нас несколько ограничивает.

Решение 2: (требует особого внимания)

DELETE FROM (
SELECT * FROM EMP_2 E2
INNER JOIN EMP E ON (E2.EMPNO=E.EMPNO)
);

Если в таблице EMP не имеется ни одного первичного ключа, то получим ошибку ORA-01752:cannot delete from view without exactly one key-preserved table
Поэтому создаем первичный ключ EMPNO для таблицы EMP. Выполняем запрос — получилось!
В документации написано что удаление в таком случае будет происходить из таблицы, первой в приджойной, т.е. в нашем случае это EMP_2. Сделаем маленькую хитрость: поменяем таблицы:

DELETE FROM (
SELECT * FROM EMP E
INNER JOIN EMP_2 E2 ON (E2.EMPNO=E.EMPNO)
);

Получаем тот же результат, что-то здесь не то. Созданим-ка первичный ключ и для таблицы EMP_2 и заново сделаем запрос.
Ура, изменения прошли в EMP, а не в EMP_2. Получается чтобы слова из документации имели силу, джоинить нужно таблицы обязательно с первичными ключами.

Другими словами: удаление будет происходить только из одной таблицы. Приоритет имеет та, у которой установлен первичный ключ. Если же обе таблицы имеют первичный ключ, то удаление произойдет из первой в конструкции JOIN.

Ссылки:
ограничение для WHERE ... IN
Интересности JOIN'a (в конце статьи)
Форум