mysql delete duplicate rows except one

Olga

Code: SQL

2021-02-12 12:47:07

DELETE FROM table_name 
WHERE 
	id IN (
	SELECT 
		id 
	FROM (
		SELECT 
			id,
			ROW_NUMBER() OVER (
				PARTITION BY field_1
				ORDER BY field_1) AS row_num
		FROM 
			table_name
		
	) t
    WHERE row_num > 1
);

Amin Parvaresh

Code: SQL

2021-02-12 12:46:17

DELETE FROM FriendsData WHERE fID 
       NOT IN ( SELECT fID FROM FriendsData 
                   GROUP BY UserID, FriendsUserID, IsSpecial, CreatedBy)

nina

Code: SQL

2021-02-12 12:43:59

DELETE FROM NAMES
 WHERE id NOT IN (SELECT * 
                    FROM (SELECT MIN(n.id)
                            FROM NAMES n
                        GROUP BY n.name) x)

MMalory

Code: SQL

2021-07-05 17:39:01

 delete test
   from test
  inner join (
     select max(id) as lastId, email
       from test
      group by email
     having count(*) > 1) duplic on duplic.email = test.email
  where test.id < duplic.lastId;

New to Communities?

Join the community

mysql delete duplicate rows except one

Tags

Related

New to Communities?