Clear Duplicate Rows

clear duplicate rows.sql

psql -d devel

-- delete duplicate rss - postgresql
DELETE FROM rss T1
    USING   rss T2
WHERE T1.ctid < T2.ctid
  AND T1.rss_source_id  = T2.rss_source_id
  AND T1.title  = T2.title
  AND T1.link  = T2.link;

\q

mysql devel
  
-- delete duplicate rss - mysql
DELETE T1 FROM rss T1
    INNER JOIN rss T2
WHERE T1.rss_id < T2.rss_id
  AND T1.rss_source_id  = T2.rss_source_id
  AND T1.title  = T2.title
  AND T1.link  = T2.link;

\q

sqlcmd -d devel

-- duplicate rss - sql server
WITH r AS (
    SELECT ROW_NUMBER() OVER(PARTITION by rss_source_id, title, link ORDER BY rss_id DESC) AS row
	FROM rss
)
DELETE FROM r
WHERE row > 1;

go

exit