How to update SQLite using LEFT JOIN to select candidate rows

Question

I have an assoc table containing columns

 local_id, remote_id, cachedData 

I can successfully run a SQLITE query that looks like

 SELECT a1.local_id, a1.remote_id FROM assoc a1 LEFT JOIN .... 

to identify specific rows in the assoc table that match my criteria.

What I would like to do is set cachedData to null in these lines.

How can i do this? Sqlite does not support UPDATE with joins; you can issue subqueries, but I cannot figure out how to get the syntax correctly; It seems uninteresting to me.

Answer 1

  UPDATE assoc SET cachedData = NULL WHERE EXISTS (SELECT * FROM otherTable WHERE otherTable.Col1 = assoc.Col1 AND otherTable.Col2 = assoc.Col1) 

Remember that this is not particularly effective.

Answer 2

If assoc has a single column as the primary key (and is assumed to be local_id ):

 UPDATE assoc SET cachedData=NULL WHERE local_id IN ( SELECT local_id FROM assoc a1 LEFT JOIN ... ); 

Answer 3

Yeah, there's already a built-in rowid!

 UPDATE assoc SET cachedData=NULL WHERE rowid IN ( SELECT rowid FROM assoc a1 LEFT JOIN ... );