Почему sql запрос удаляет все дубликаты

Question

Допустим есть таблица с столбцом val у которой три записи 1,1,1. Я хочу удалить дубликаты и оставить одну запись 1. Но мой запрос удаляет все. Почему?

delete from test t1 where val = ( select val from test t2 where t1.val = t2.val group by val having count(val) > 1 )

Answer 1

Удаление происходит, так как в подзапрос попадают все строки с данным значением val.

Если есть какое-то дополнительное поле, по которому можно различать разные записи, например таймстемп создания записи created_at, можно найти соответствующее минимальное значение и удалять всё что больше этого таймстемпа, используя DELETE USING

DELETE FROM test t1
USING (
    SELECT val, MIN(created_at) mntime
    FROM test t2
    GROUP BY val
    HAVING COUNT(*)>1
) d
WHERE t1.val = d.val AND t1.created_at > d.mntime;

Если такого отдельного поля нет, можно аналогично использовать встроенный идентификатор записи ctid:

DELETE FROM test t1
USING (
    SELECT val, MIN(ctid) mnid
    FROM test t2
    GROUP BY val
    HAVING COUNT(*)>1
) d
WHERE t1.val = d.val AND t1.ctid > d.mnid;

Решение из PostgresQL Wiki: Deleting Duplicates использует оконную функцию и предполагает, что есть некое ключевое поле id -- идентификатор записи:

DELETE FROM test 
WHERE id IN (
    SELECT
        id
    FROM (
        SELECT
            id,
            row_number() OVER (PARTITION BY val ORDER BY id) as rnum
        FROM test
    ) t
    WHERE t.rnum > 1
);

Answer 2

Если описать в общих чертах, что DELETE выполняется в два этапа:

Выполняется SELECT с аналогичным условием WHERE, который готовит список записей.
Выполняется собственно удаление по ранее подготовленному списке.

Могу ошибаться, но, вроде бы, это требование стандарта SQL.

Если бы это делалось не так, то появлялись бы побочные эффекты, в частности ваш запрос в мог оставить любую из 3-х записей, и при каждом запуске разную. В некоторых устаревших СУБД можно встретить ожидаемое вами поведение, и в них есть костыль в виде предложения ORDER BY у DELETE

БЛОГ НА HUSL

Почему sql запрос удаляет все дубликаты

Ответы (2 шт):