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

2022-01-17 00:00:00 join sql-update 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 中会出现语法错误.

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

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

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

推荐答案

你不能.SQLite 不支持 UPDATE 语句中的 JOIN.

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.

相关文章