MySQL delete rows with subquery

Question

I want to delete duplicated rows from my link table. This select query here does work:

SELECT *
from LINKS t1
WHERE EXISTS (
    SELECT *
    from LINKS t2
    where t2.cntid = t1.cntid
        and t2.title= t1.title
        and t2.lnkid > t1.lnkid
);

when I change the same query to delete:

    DELETE from LINKS t1
WHERE EXISTS (
    SELECT *
    from LINKS t2
    where t2.cntid = t1.cntid
        and t2.title= t1.title
        and t2.lnkid > t1.lnkid
);

it does not work anymore and states: ERROR 1064 (42000): You have an error in your SQL syntax

What is the issue here? Can someone please help to fix the query?

Because DELETE and SELECT have different syntax, aren't them?.. — Alma Do
– Alma Do, Commented Nov 21, 2013 at 11:09

Jason Heo · Accepted Answer · 2013-11-21 11:39:42Z

2

you can do that only using JOIN without EXISTS and inline view as follows:

DELETE t1
FROM LINKS t1, LINKS t2
WHERE t2.cntid = t1.cntid
    AND t2.title= t1.title
    AND t2.lnkid > t1.lnkid

This is faster than using EXISTS. of course, you SELECT can be converted like this.

SELECT *
from LINKS t1, LINKS t2
WHERE t2.cntid = t1.cntid
    AND t2.title= t1.title
    AND t2.lnkid > t1.lnkid

If you try DELETE, plz set autocommit = '0'

edited Nov 21, 2013 at 11:39

answered Nov 21, 2013 at 11:11

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

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

2 Comments

sub Over a year ago

Thank you, the delete query is working now. However, since there are a couple of million duplicated rows, the delete query is running for more than an hour now and no end in sight. Can I speed things up somehow?

Jason Heo Over a year ago

@sub hard to typing. I am using smartphone moreover my english is not good. Can you try my answer on other question? I really want to know this nswer improve you. Plz let me know. stackoverflow.com/questions/19929000/…

valex · Accepted Answer · 2013-11-21 11:19:40Z

1

Try:

DELETE t1 from LINKS t1 
   WHERE EXISTS (SELECT * from (select * from LINKS) t2 
                   where t2.cntid = t1.cntid 
                   and t2.title= t1.title 
                   and t2.lnkid > t1.lnkid);

SQLFiddle demo

edited Nov 21, 2013 at 11:19

answered Nov 21, 2013 at 11:10

valex

24.2k7 gold badges47 silver badges62 bronze badges

Collectives™ on Stack Overflow

MySQL delete rows with subquery

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related