CODEWITHSUNDEEP
mysqlsubquerydelete-row
sub
sub

Reputation: 167

MySQL delete rows with subquery

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?

Upvotes: 1

Views: 238

Answers (2)

Jason Heo
Jason Heo

Reputation: 10246

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'

Upvotes: 2

valex
valex

Reputation: 24144

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

Upvotes: 1

Related Questions