MySQL Deleting duplications

Question

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

logicbloke · Accepted Answer · 2016-09-05T08:58:48.733

1

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;

edited Sep 05 '16 at 08:58

answered Sep 05 '16 at 08:47

logicbloke

139
3
13

Hi mate, the select works perfect but i'm getting this error, #1093 - Table 'costar' is specified twice, both as a target for 'DELETE' and as a separate source for data – Alex Banerjee Sep 05 '16 at 08:54
1

Create a 2nd temporary table **CREATE TABLE costar_tmp AS SELECT id, pnum FROM costar;** Then replace the costar in the subquery with costar_tmp and try again. – logicbloke Sep 05 '16 at 08:57
I have ran it and its deleted about 750 lines but when i run 'SELECT pnum FROM costar group by pnum having count(*) >= 2' to check how many are left im still getting 1500 , i had 1800 before. – Alex Banerjee Sep 05 '16 at 09:08

score 1 · Answer 2 · answered Sep 05 '16 at 08:49

1

If you have a Id, you could do something like the following.

  delete costar
  where  Id not in (
    select min(Id)
    from   costar
    group  by pnum
  )

answered Sep 05 '16 at 08:49

Valentin Sky

132
1
9

MySQL Deleting duplications

2 Answers2