CODEWITHSUNDEEP
mysqlsql
john doe
john doe

Reputation: 435

How to delete duplicate rows in MySQL table?

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)

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 1

Related Questions