CODEWITHSUNDEEP
sqlitesubquerysql-delete
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7571

How can I delete multiple rows from a table in SQLite

I have a sub query as this:

SELECT INTER1.LID1 AS ILID1,INTER1.LID2 AS ILID2 FROM
(SELECT L1.ID1 AS LID1, L1.ID2 AS LID2 FROM Friend F1
INNER JOIN Likes L1
ON F1.ID1 = L1.ID1 AND F1.ID2 = L1.ID2) INTER1
LEFT JOIN Likes L2
ON (INTER1.LID1 = L2.ID2 AND INTER1.LID2 = L2.ID1)
WHERE (L2.ID1 IS NULL AND L2.ID2 IS NULL)

The output of this is as follows:

     ID1    ID2

    1641    1468
    1911    1247

Now I have to delete both these rows from a table LIKES whose format is as follows:

  ID1       ID2

 1689       1709
 1709       1689
 1782       1709
 1911       1247
 1247       1468
 1641       1468

I have tried to accomplish this using the following query in SQLite:

DELETE FROM Likes 
WHERE EXISTS 
(SELECT INTER1.LID1 AS ILID1,INTER1.LID2 AS ILID2 FROM
(SELECT L1.ID1 AS LID1, L1.ID2 AS LID2 FROM Friend F1
INNER JOIN Likes L1
ON F1.ID1 = L1.ID1 AND F1.ID2 = L1.ID2) INTER1
LEFT JOIN Likes L2
ON (INTER1.LID1 = L2.ID2 AND INTER1.LID2 = L2.ID1)
WHERE (L2.ID1 IS NULL AND L2.ID2 IS NULL));

but it ain't working as intended, it is not deleting only those rows.

Then I tried to work around this problem using INNER JOIN but the functionality of INNER JOIN in association with DELETE function isn't there in SQLite.

How can I make this work.

Kindly help

Upvotes: 1

Views: 545

Answers (1)

CL.
CL.

Reputation: 180070

Just compare the two values at once:

DELETE FROM Likes
WHERE (ID1, ID2) IN (SELECT ... your original query ...);

Row values require SQLite 3.15 or later. If you're using an earlier version, you have to look up the rows to delete with a single-column primary key (rowid, or whatever you have declared as INTEGER PRIMARY KEY) in a separate step:

DELETE FROM Likes
WHERE rowid IN (SELECT Likes.rowid
                FROM Likes
                JOIN (SELECT ... your original query ...) AS IDs
                  ON Likes.ID1 = IDs.ILID1
                 AND Likes.ID2 = IDs.ILID2);

Upvotes: 1

Related Questions