在 SQLite 上连接表时如何进行更新？

我试过:

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

还有:

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

两者都可以使用 MySQL，但是在 SQLite 中出现了语法错误。

如何使这个 UPDATE/JOIN 与 SQLite 版本3.5.9一起工作？

sqlite
join
sql-update

54938 次浏览

小开

最佳答案

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.

小开

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