bloginfo

SQL : how to remove duplicate rows from tables

Nov 23rd, 2020 (edited)
1,097
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.41 KB | None | 0 0
  1. DELETE FROM ttrss_entries
  2. WHERE id IN
  3. (
  4.     WITH
  5.         req_titles AS
  6.         (
  7.             SELECT
  8.                 id,
  9.                 MD5(title) md5title
  10.             FROM
  11.                 ttrss_entries
  12.         ),
  13.         req_doublons AS
  14.         (
  15.             SELECT md5title,MIN(ID) minid FROM req_titles
  16.             GROUP BY md5title
  17.             HAVING COUNT(md5title)>1   
  18.         )
  19.     SELECT id FROM req_titles
  20.     WHERE md5title IN
  21.     (
  22.         SELECT md5title FROM req_doublons
  23.     )
  24.     EXCEPT
  25.     SELECT minid FROM req_doublons
  26. );
Add Comment
Please, Sign In to add comment