Tutorial :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  

And:

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

Both works with MySQL, but those give me a syntax error in SQLite.

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


Solution:1

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.


Solution:2

You can also use REPLACE then you can use selection with joins. Like this:

REPLACE INTO clusure    SELECT sel.col1,sel.col2,....,sel.checked --checked should correspond to column that you want to change  FROM (   SELECT *,0 as checked FROM closure LEFT JOIN item ON (item_id = id)    WHERE ancestor_id = 1) sel  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »