Alex Banerjee Alex Banerjee - 2 months ago 8
MySQL Question

MySQL Deleting duplications

I want to sort my database duplicates out via 'Telephone' I can only seem to delete them all instead of keeping one of the numbers

My code currently:

delete from costar where (SELECT pnum FROM costar group by pnum having count(*) >= 2)


any help will be much appreciated

thanks

Answer

The condition in your WHERE clause will always be true even if you group by id and pnum which is the correct way to visualize duplicates. Try this instead:

DELETE FROM costar WHERE id IN
(
SELECT c2.id 
FROM costar_tmp c1, costar_tmp c2
WHERE c1.id <> c2.id
AND c1.pnum = c2.pnum
)

EDIT:

Updating solutions with the use of a temporary table that can be created with:

CREATE TABLE costar_tmp AS SELECT id, pnum FROM costar;
Comments