94

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 ?

shA.t
  • 16,580
  • 5
  • 54
  • 111
Bite code
  • 578,959
  • 113
  • 301
  • 329
  • This is already answered by http://stackoverflow.com/questions/3845718/sql-how-to-update-table-values-from-another-table-with-the-same-user-name – Gad D Lord Aug 26 '14 at 21:09

2 Answers2

130

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.

Andrew Watt
  • 2,757
  • 2
  • 20
  • 18
  • 21
    Where this gets hairy is when what you need to do is copy a column from one table to another in order to reverse the direction of an association. where in MySQL you might do something like, create the foos.bar_id column, then `update foos join bars on bars.foo_id = foos.id set foos.bar_id = bars.id`, then drop the bars.foo_id column... how could this be done in SQLite? If anyone knows, I could sure use it. – hoff2 Jan 14 '11 at 23:52
  • @hoff2 Actually, this is probably the simplest way to do this. In my case I didn't even need to WHERE EXISTS part: http://stackoverflow.com/a/3845931/847201 – ACK_stoverflow Apr 15 '16 at 01:14
7

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

REPLACE INTO closure 
 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
Zakaria
  • 4,715
  • 2
  • 5
  • 31
Roman Nazarevych
  • 7,513
  • 4
  • 62
  • 67