updated method of deleting duplicates

PHOTO EMBED

Tue May 03 2022 03:03:20 GMT+0000 (Coordinated Universal Time)

Saved by @Bambibo9799

--------
CREATE TABLE T (name varchar(10));
INSERT INTO T VALUES
('John'),
('Smith'),
('John'),
('Smith'),
('Smith'),
('Tom');
--------------------first method deleting duplicate
WITH t_deleted AS
(DELETE FROM T returning *), -- 1st step
t_inserted AS
(SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk
    FROM t_deleted) -- 2nd step
INSERT INTO T SELECT name FROM t_inserted 
WHERE rnk=1; -- 3rd step 
--(here we move the search condition from the step 2 to reduce the code)

SELECT * FROM T;
-------------------------second method
select "name",row_number()over(partition by name)
into t_duplicate_fixed
from t; 

delete from t_duplicate_fixed
where row_number >1
-------------------------third method 
delete from t
where ctid in (select ctid
				from (select ctid,"name",row_number()over(partition by name)as rn from t) t2
				where rn >1) 

---------------------------------------4th method
DELETE FROM t a USING (
      SELECT MIN(ctid) as ctid, name,count(*)
        FROM t 
        GROUP BY name HAVING COUNT(*) > 1
      ) b
      WHERE a.name = b.name 
      AND a.ctid <> b.ctid	
content_copyCOPY