0

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?

Rahul Kumar
  • 139
  • 3
  • 10
  • You can't delete from the result of a query. Query 1 can be replaced with `delete from table2` and query 2 can be replaced with `delete from table2 where col1 = 'something'` - but I assume that's not what you actual underlying problem is. What exactly are you trying to do? Please provide some sample data and the expected results –  Oct 17 '20 at 10:56
  • i am trying to delete duplicate rows using row_number as following: with rohilla as (select row_number() over(partition by fruit order by fruit) row_num, fruit from rk) delete from rohilla where row_num >1; – Rahul Kumar Oct 17 '20 at 10:57
  • https://stackoverflow.com/search?q=[postgresql]+[duplicates]+delete –  Oct 17 '20 at 10:58
  • the problem is i do not have identifier on my table, so can you post sol for that – Rahul Kumar Oct 17 '20 at 11:00
  • https://stackoverflow.com/questions/26769454/how-to-delete-duplicate-rows-without-unique-identifier –  Oct 17 '20 at 11:00
  • If you don't have a Primary Key (*identifier*), your table is meaningless anyway. [BTW: there always is a ctid] – wildplasser Oct 17 '20 at 11:08
  • Please post your table schema and logic of identifying duplicate. – Akhilesh Mishra Oct 17 '20 at 11:36

2 Answers2

1

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;
Somy
  • 1,474
  • 1
  • 4
  • 13
0

You cannot delete from a CTE in Postgres. Clearly, I'm sure you are aware that you can do:

delete from table2
    where col1 = 'something';

If you want to involve a CTE, then you can use some sort of filtering, typically on a primary key:

with table1 as (
      select * from table2
     )
delete from table2 t2 using
     table1 t1
     where t1.<primary key> = t2.<primary key> and
           t1.col1 = 'something';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786