How to delete duplicate rows in MySQL table?

Question

I am using the following query, which I saw from another stackoverflow question but I am getting error.

delete from mytable
 where myid not in (
    select max(myid)
      from mytable
group by myid2)

Error:

#1093 - Table 'mytable' is specified twice, both as a target for 'DELETE' and as a separate source for data

Edit 2:

I also tried this query:

delete from mytable
 where myid in (
    SELECT
    myid, COUNT(*)
FROM
    mytable
GROUP BY
    myid2
HAVING 
    COUNT(*) > 1)

And got this error:

#1241 - Operand should contain 1 column(s)

Your query doesn't make sense. You have max(myid) and group by myid. This will delete no rows. — Gordon Linoff
– Gordon Linoff, Commented May 26, 2019 at 12:13
I will try that. I changed myid in group by to myid2 (they are different id, and contain same duplicate rows). But I sitll get the same error — john doe
– john doe, Commented May 26, 2019 at 12:19

Gordon Linoff · Accepted Answer · 2019-05-26 15:15:39Z

1

In MySQL, you need to use a JOIN for this purpose. I assume you mean something like this:

delete t
   from mytable t left join
        (select max(myid) as myid
         from mytable
         group by myid2
        ) tt
        on t.myid = tt.myid
   where tt.myid is null;

Where ? is whatever you really want to group by. Your version will not delete anything because the group by and max() use the same column.

edited May 26, 2019 at 15:15

answered May 26, 2019 at 12:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

john doe Over a year ago

I will try that. I changed myid in group by to myid2 (they are different id, and contain same duplicate rows). But I sitll get the same error

john doe Over a year ago

I am getting this error Unexpected keyword. (near "join" at position 9)

Gordon Linoff Over a year ago

@johndoe . . . That was a typo.

Collectives™ on Stack Overflow

How to delete duplicate rows in MySQL table?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related