updated method of deleting duplicates
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
Comments