sqlite DELETE (based on 2 tables) syntax (version 3.36)

Question

I tried to delete an entry from table1 based on criteria on table2. (id in table1 is foreign key from table2)

I tried all those below and all returned with syntax errors.

take 1:

delete  table1.* from table1 inner join table2 on table1.id=table2.id where table2.column3=21 and table2.column4=59;

Error: near "table1": syntax error

take 2:

delete  table1 from table1 inner join table2 on table1.id=table2.id where table2.column3=21 and table2.column4=59;

Error: near "table1": syntax error

take 3:

delete from table1 inner join table2 on table1.id=table2.id where table2.column3=21 and table2.column4=59;

Error: near "inner": syntax error

Anyone knows what are the correct syntax?

If this is the wrong approach, what is the correct way to achieve my goal?

Really appreciate it.

I don't believe SQLite supports `JOIN` in outer query of `DELETE` statements. — Parfait, Mar 24 '22 at 00:16

score 0 · Answer 1 · answered Mar 24 '22 at 00:22

0

No Join in SqlLite DELETE, by the look of it, but you could use exists:

delete  
from table1 
where exists
  (select 1 
   from table2 
   where    table1.id=table2.id
        and table2.column3=21 
        and table2.column4=59
  );

answered Mar 24 '22 at 00:22

tinazmu

3,880
2
7
20

score 0 · Answer 2 · answered Mar 24 '22 at 00:25

Since SQLite does not support JOIN yet in outer query of DELETE statements, consider using subquery with IN or EXISTS:

DELETE FROM table1 
WHERE id IN (
   SELECT id 
   FROM table2
   WHERE table2.column3 = 21 
     AND table2.column4 = 59
)

DELETE FROM table1 
WHERE EXISTS (
   SELECT 1
   FROM table2
   WHERE table1.id = table.id
     AND table2.column3 = 21 
     AND table2.column4 = 59
)

sqlite DELETE (based on 2 tables) syntax (version 3.36)

2 Answers2