[GENERAL] Delete Duplicates with Using

Поиск
Список
Период
Сортировка
От Igal @ Lucee.org
Тема [GENERAL] Delete Duplicates with Using
Дата
Msg-id d2205343-8d46-c6be-05e4-72f0fffba644@lucee.org
обсуждение исходный текст
Ответы Re: [GENERAL] Delete Duplicates with Using  (legrand legrand <legrand_legrand@hotmail.com>)
Re: [GENERAL] Delete Duplicates with Using  (Andreas Kretschmer <andreas@a-kretschmer.de>)
Список pgsql-general

Hello,

I run the SQL query below to delete duplicates from a table.  The subquery is used to identify the duplicated rows (row_num is a BIGSERIAL column).

/** delete older copies of duplicates */
DELETE FROM table_with_duplicates AS T
WHERE row_num IN (
    SELECT     T1.row_num
    FROM    table_with_duplicates  AS T1
        JOIN table_with_duplicates AS T2
            ON         T1.column_1 = T2.column_1
                AND T1.column_2 = T2.column_2
                AND T1.column_3 = T2.column_3
                AND T1.row_num < T2.row_num
);

Can anyone tell me how to rewrite that query to use the USING clause and hopefully remove the subquery?

The documentation mentions USING but there is no example and the only examples I found online are very trivial.

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org

В списке pgsql-general по дате отправления:

Предыдущее
От: Thomas Kellerer
Дата:
Сообщение: Re: [GENERAL] Multiple Schemas vs. Multiple Databases
Следующее
От: legrand legrand
Дата:
Сообщение: [GENERAL] Where to find development builds of pg for windows