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

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.

CodePudding user response：

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
  );

CodePudding user response：

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
)