dev=# select ctid,* from test;
ctid | id | name
-------+----+------
(0,1) | 1 | RC
(0,2) | 2 | RC
(0,3) | 1 | RC
(0,4) | 2 | RC
(4 rows)
dev=# select ctid,* from test where ctid not in ( select min(ctid) from test group by id,name);
ctid | id | name
-------+----+------
(0,3) | 1 | RC
(0,4) | 2 | RC
(2 rows)
dev=# delete from test where ctid not in ( select min(ctid) from test group by id,name);
DELETE 2
dev=# select ctid,* from test;
ctid | id | name
-------+----+------
(0,1) | 1 | RC
(0,2) | 2 | RC
(2 rows)
ctid | id | name
-------+----+------
(0,1) | 1 | RC
(0,2) | 2 | RC
(0,3) | 1 | RC
(0,4) | 2 | RC
(4 rows)
dev=# select ctid,* from test where ctid not in ( select min(ctid) from test group by id,name);
ctid | id | name
-------+----+------
(0,3) | 1 | RC
(0,4) | 2 | RC
(2 rows)
dev=# delete from test where ctid not in ( select min(ctid) from test group by id,name);
DELETE 2
dev=# select ctid,* from test;
ctid | id | name
-------+----+------
(0,1) | 1 | RC
(0,2) | 2 | RC
(2 rows)
This comment has been removed by the author.
ReplyDeletecheck the outer query where clause before proceeding the delete
ReplyDelete