根据另一个表中的数据更新 SQLite 表 [英] Update SQLite table based on data in another table
问题描述
我在 SQLite 中有两个表,看起来像这样
I have two tables in SQLite which look like this
TABLE_X
____________________
| id | C1 | C2 | C3 | C4 |
| 10 | 99 | 03 | 04 | 00 |
| 60 | 88 | 20 | 30 | 40 |
TABLE_Y
___________
| id | C2 |
| 10 | 11 |
| 60 | 22 |
我正在尝试编写查询以根据表 Y 中的记录更新表 X 上的记录更新条件如下
I am trying to write query to update records on Table X based on records in Table Y The condition for the updateis something like the following
update table_x
set table_x.c1 = 100,
table_x.c2 = table_y.c2
where table_x.id = table_y.id
但是当我尝试这样做时,我收到一条错误消息
But when I try to do this I get an error message saying
没有这样的列:table_y.c2
推荐答案
已删除的答案关于错误原因是正确的:关系标识符必须引入(例如使用 FROM/JOIN)可以使用之前的查询.
The deleted answer was correct about the cause of the error: a relation identifier must be introduced (e.g. with FROM/JOIN) in a query before it can be used.
虽然SQLite不支持UPDATE..JOIN
(所以没有办法直接引入查找关系),可以使用依赖子查询来模拟效果:
While SQLite does not support UPDATE..JOIN
(so there is no way to introduce the lookup relation directly), a dependent sub-query can be used to simulate the effect:
update table_x
set c1 = 100,
c2 = (select y.c2 from table_y as y
where y.id = table_x.id)
注意,不像一个正确的UPDATE..JOIN
,如果子选择未能找到匹配项,则将分配NULL.
Note that unlike a proper UPDATE..JOIN
, if the sub-select fails to find a match then NULL will be assigned.
天啊.
这篇关于根据另一个表中的数据更新 SQLite 表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!