Search code examples
mysqlsqlsql-delete

MySQL DELETE query with conditions

I have a table PEOPLE, with columns 'firstName' 'lastName' (varchars) and 'deleted' (bit) amongst others.

I want to delete from this table, entries that have the property TRUE for deleted, but only if they share their exact firstName and lastName with another, separate, entry in the table.

In other words, remove from the table 'deleted' people, but only if they are a duplicate.

Not sure how to do this, and especially not how to do it quickly. Any help is appreciated, thanks.

Solution 
  • DELETE FROM people
WHERE EXISTS (
    SELECT *
    FROM people p2
    WHERE people.firstName = p2.firstName AND people.lastName = p2.lastName
    GROUP BY firstName, lastName
    HAVING COUNT(*)>1
)
AND deleted = 1 -- True