iamsophia iamsophia - 16 days ago 5
MySQL Question

Delete... Left Join query in sqlite

I am developing an iOS and Android application using Xamarin Forms. I have the following code:

db2.Execute("DELETE w FROM Word AS w" +
" LEFT JOIN WordSource AS ws ON ws.WordId = w.WordId" +
" WHERE ws.WordId IS NULL");


But I kept getting syntax error near w. Can someone let me know what I'm doing wrong? Is
DELETE... JOIN...
query not supported in sqlite? If so, how can I accomplished this code in sqlite?

Answer

A simple way is based on an a in clause and subselect with left join

db2.Execute( "DELETE  FROM Word WHERE id in (  
                SELECT id FROM Word as W  
                LEFT JOIN WordSource AS ws ON ws.WordId = w.WordId 
                WHERE ws.WordId IS NULL
              )");
Comments