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, Nov 21 '13 at 11:09

Jason Heo · Accepted Answer · 2013-11-21T11:39:42.933

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 '13 at 11:39

answered Nov 21 '13 at 11:11

Jason Heo

9,956
2
36
64

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? – sub Nov 21 '13 at 15:03
@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. http://stackoverflow.com/questions/19929000/slow-mysql-query-on-update-statement/19929325#19929325 – Jason Heo Nov 21 '13 at 15:16

valex · Answer 2 · 2013-11-21T11:19:40.653

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 '13 at 11:19

answered Nov 21 '13 at 11:10

valex

23,966
7
43
60

MySQL delete rows with subquery

2 Answers2