How do I make an UPDATE while joining tables on SQLite ?

Question

I tried :

UPDATE closure JOIN item ON ( item_id = id ) SET checked = 0 WHERE ancestor_id = 1

Then :

UPDATE closure, item SET checked = 0 WHERE ancestor_id = 1 AND item_id = id

Both works with MySql but gives me a syntax error in SQLite.

How can I make this UPDATE / JOIN works with SQLite version 3.5.9 ?

Answer 1

I haven't use SQLLite but you could try this syntax:

UPDATE closure SET checked = 0 FROM closure

JOIN item ON ( item_id = id )

WHERE ancestor_id = 1

Answer 2

You can't. SQLite doesn't support JOINs in UPDATE statements.

But, you can probably do this with a subquery instead:

UPDATE closure SET checked = 0 
WHERE item_id IN (SELECT id FROM item WHERE ancestor_id = 1);

Or something like that; it's not clear exactly what your schema is.