Delete... Left Join query in sqlite

Question

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?

score 6 · Accepted Answer · answered Nov 19 '16 at 07:48

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

Delete... Left Join query in sqlite

1 Answers1