Duplicated records - Delete statement does not work-CodePudding

I am facing an issue related to removing the records from my table. Table structure:id | first_name | last_name | info | timestamp

My goal is to remove all duplicated records which have got this same first_name and timestamp. Example:

10,Mariusz,Jablko,Smieszny,1635837167
198,Mariusz,Smieszek,Zwariowany,1635837167
285,Mariusz,Ogien,Smutny,1635837168

So, I would like to remove the record with id = 198 (This same first_name and timestamp as 10).

I tried something like this:

DELETE FROM people p1 
 INNER JOIN people p2 on p1.first_name = p2.first_name 
        AND p1.timestamp = p2.timestamp 
      WHERE p1.id < p2.id

I do not know why, but it does not work. Could you please let me know what I am doing wrong?

Thanks

CodePudding user response：

If you are using SQL Server the use Common table expression, the following query will delete all duplicate records:

WITH CTE AS(
   SELECT *,
       RN = ROW_NUMBER()OVER(PARTITION BY first_name ,timestamp  ORDER BY timestamp )
   FROM people 
)
DELETE FROM CTE WHERE RN > 1

CodePudding user response：

You must tell the DBMS which of the two tables to delete from (DELETE p1 FROM ...). And in order to keep the lower ID you need WHERE p1.id > p2.id instead of WHERE p1.id < p2.id.

DELETE p1 
 FROM people p1 
 INNER JOIN people p2 ON p1.first_name = p2.first_name 
                     AND p1.timestamp = p2.timestamp 
                     AND p1.id > p2.id;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=506708ccceca6217d783b8477f3ec247