CODEWITHSUNDEEP
mysqlsqlsql-delete
Yottagray
Yottagray

Reputation: 2582

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.

Upvotes: 2

Views: 6917

Answers (3)

Clockwork-Muse
Clockwork-Muse

Reputation: 13046

If your table has a unique primary key (... will depend on design...), then this is a viable alternative to needing to count the occurrances of entries:

DELETE FROM people as A
WHERE deleted = 1
AND EXISTS (SELECT '1'
            FROM people as B
            WHERE B.id <> A.id
            AND A.firstName = B.firstName
            AND A.lastName = B.lastName)

This may have slightly better performance than counting rows. Please note that this query will likely suffer the same possible issue present in the previous answer; specifically, if there are two or more 'deleted' rows, and no 'non-deleted', both of them will probably be removed (leaving you with no rows!). If the intent of the query is only to remove 'deleted' rows when there is a 'non-deleted' equivalent row, add AND B.deleted = 0 as part of the inner WHERE clause.

Upvotes: 1

niktrs
niktrs

Reputation: 10066

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

Upvotes: 3

Naftali
Naftali

Reputation: 146302

Here is a rudimentary way of doing it:

http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table/

Basically:
1. Create a new table with GROUP BY.
2. Delete old table.
3. Rename new table.

Upvotes: 0

Related Questions