CODEWITHSUNDEEP
sqlitesql-delete
Jan Turoň
Jan Turoň

Reputation: 32912

SQLite: delete all records from table specified in another table

Having tables like this

mytab       deltab
---------   --------
id | name   id | name
 1 | Ann     2 | Bob
 2 | Bob     3 | Cindy
 3 | Cindy
 4 | Dave

I'd like to perform query which deletes all records in mytab specified in deltab, so only Ann and Dave would be left in mytab.

While there is MySQL multiple-table delete syntax, looks like there is no such thing in SQLite delete syntax.

I am considering REPLACE syntax with select-stmt and mark the rows which will be deleted (like set these names to NULL) in DELETE query after. I wonder if there is more effective way?

Upvotes: 3

Views: 2977

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

Try this:

DELETE FROM mytab       
WHERE EXISTS (SELECT *
              FROM deltab
              WHERE deltab.ID = mytab.ID)

Upvotes: 2

Lokesh Kumar
Lokesh Kumar

Reputation: 418

Try this query

Delete from mytab where id in (select id from deltab);

Upvotes: 5

Related Questions