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

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

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

1
CREATE TABLE EMP_2 AS (SELECT * FROM EMP);

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

1
INSERT INTO EMP_2 SELECT * FROM EMP;

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

Решение 1:

1
2
3
4
5
6
7
8
DELETE FROM
    EMP_2 E2
WHERE
    E2.EMPNO IN (
        SELECT
            E.EMPNO
        FROM EMP E
    );

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

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

1
2
3
4
5
6
7
8
9
10
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. Сделаем маленькую хитрость: поменяем таблицы:

1
2
3
4
5
6
7
8
9
10
11
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 (в конце статьи)
Форум