Есть две таблицы 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 (в конце статьи)
Форум