SQLite语法纠正 - UPDATE SELECT带有WHERE EXISTS条件

Question

我正在尝试更新SQLite表中列中的选定值。我只想更新主表中符合条件的单元格，并且这些单元格必须被更新为从子表中获取的各自的值。

我尝试了以下语法，但我只得到了单个单元格的更新。我还尝试了其他替代方法，其中所有单元格都被更新为子表的第一个选定值。

UPDATE maintable
SET value=(SELECT subtable.value FROM maintable, subtable
WHERE  maintable.key1=subtable.key1 AND maintable.key2=subtable.key2)
WHERE EXISTS (SELECT subtable.value FROM maintable, subtable
WHERE  maintable.key1=subtable.key1 AND maintable.key2=subtable.key2)

什么是适当的语法？

- AndBB

Answer 1

你可以使用update select来实现这一点，但一次只能更新一个字段。 如果Sqlite支持在更新语句中使用关联操作会更好，但它不支持。

以下是一个相关的SO问题：如何在SQL Server中使用SELECT进行UPDATE？，但是只适用于SQL Server。那里有类似的答案。

sqlite> create table t1 (id int, value1 int);
sqlite> insert into t1 values (1,0),(2,0);
sqlite> select * from t1;
1|0
2|0
sqlite> create table t2 (id int, value2 int);
sqlite> insert into t2 values (1,101),(2,102);
sqlite> update t1 set value1 = (select value2 from t2 where t2.id = t1.id) where t1.value1 = 0;
sqlite> select * from t1;
1|101
2|102

- Jess

Answer 2

SQLite默认情况下不支持带有joins的update操作，但我们可以使用with-clause+column-name-list+select-stmt来实现类似的功能。更多信息可参考https://www.sqlite.org/lang_update.html。

CREATE TABLE aa (
_id INTEGER PRIMARY KEY,
a1 INTEGER,
a2 INTEGER);

INSERT INTO aa  VALUES (1,10,20);
INSERT INTO aa  VALUES (2,-10,-20);
INSERT INTO aa  VALUES (3,0,0);

--a bit unpleasant because we have to select manually each column and it's just a lot to write
WITH bb (_id,b1, b2)  
AS  (SELECT _id,a1+2, a2+1 FROM aa WHERE _id<=2) 
UPDATE aa  SET a1=(SELECT b1 FROM bb WHERE bb._id=aa._id),a2=(SELECT b2 FROM bb WHERE bb._id=aa._id)
WHERE _id in (SELECT _id from bb);

--soo now it should be (1,10,20)->(1,12,21) and (2,-10,-20)->(2,-8,-19), and it is
SELECT * FROM aa;


--even better with one select for each row!
WITH bb (_id,b1, b2)  
AS  (SELECT _id,a1+2, a2+1 from aa WHERE _id<=2)
UPDATE aa  SET (a1,a2)=(SELECT b1,b2 FROM bb WHERE bb._id=aa._id)
WHERE _id in (SELECT _id from bb);

--soo now it should be (1,12,21)->(1,14,22) and (2,-8,-19)->(2,-6,-18), and it is
SELECT * FROM aa;


--you can skip the WITH altogether
UPDATE aa SET (a1,a2)=(SELECT bb.a1+2, bb.a2+1 FROM aa AS bb WHERE aa._id=bb._id)
WHERE _id<=2;

--soo now it should be (1,14,22)->(1,16,23) and (2,-6,-18)->(2,-4,-17), and it is
SELECT * FROM aa;

希望SQLite足够聪明，能根据文档查询增量。当使用一个select设置多个列（情况2和3）且没有有效ID（没有where _id in行）时，将会报错，不能使用ON IGNORE忽略，情况1将为所有ID> 2的行设置为null，这也是不好的。请注意保留HTML标记。

- Then Enok

---

情况1是指有一个大查询，它执行粗略的、毫无特殊意义的许多无脑查询来设置行。情况2是花哨而漂亮的。情况3则简短而美观。 - Then Enok

对于Android用户，请注意情况2和3仅适用于Android 8.0 API 26（那时sqllite具有v3.15）。 - Then Enok

Answer 3

在这种情况下，每个主表中的行只更新子表中的一个值。错误出现在将子表包含到SELECT语句中时。

UPDATE maintable
SET value=(SELECT subtable.value 
             FROM subtable
             WHERE  maintable.key1=subtable.key1 );

- Marcelo

Answer 4

您需要使用INSERT OR REPLACE语句，类似以下内容：

假设maintable有4列：key，col2，col3，col4， 并且您想要将col3更新为subtable中匹配的值。

INSERT OR REPLACE INTO maintable
SELECT maintable.key, maintable.col2, subtable.value, maintable.col4
FROM maintable 
JOIN subtable ON subtable.key = maintable.key

- Noah

---

不错的回答，但你不需要使用INSERT OR REPLACE。我找到了另一个答案 :) 此外，INSERT OR REPLACE需要你拥有主键的所有字段，对吗？ - Jess

@Jess 答案在哪里？ - cikatomo

@cikatomo 抱歉，我不明白。我以前从未使用过 INSERT OR REPLACE。 - Jess