Search code examples
sqlpostgresqlduplicatessql-delete

How to use delete clause after with clause in postgresql?

Query 1:

with table1 as (select * from table2) delete * from table1;

Query 2:

with table1 as (select * from table2) delete * from table1 where col1 = 'something';

Both of the above query are returning error after execution? Can someone help me here?

Solution

  • Based on the problem statement in the comment section, you can use the query below to delete duplicates

    delete from rohilla a using rohilla b where a=b and a.ctid < b.ctid;

    Using with clause you can do the following to delete duplicates. (Col1 below can be any column, if entire row is duplicated)

    WITH x AS 
( 
         SELECT   col1, 
                  Min(ctid) AS min 
         FROM     rohilla 
         GROUP BY col1
         HAVING   Count(col1) > 1 ) 
DELETE 
FROM   rohilla b 
using  x 
WHERE  x.col1 = b.col1
AND    x.min <> b.ctid;