SQLite: delete all records from table specified in another table

3
votes

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?

2
sqlitesql-delete

2 Answers

4
votes

Try this query

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

Try this:

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