我有2个sqlite表：
table1(id INTEGER AUTOINCREMENT,状态TEXT,名称TEXT);
| id | status | name | |----|-----------|------| | 1 | pending | xyz | | 2 | completed | abc |
table2(状态TEXT,名称TEXT,trans_id INTEGER);
| trans_id | status | name | |----------|-----------|------| | 1 | refunded | cvb | | 2 | cancelled | asd |
我想更新从table2到table1的状态和名称,其中table1.id = table2.trans_id
我有这个问题：
UPDATE table1 SET status = (SELECT t2.status FROM table1 t1,table2 t2 WHERE t1.id = t2.trans_id),name = (SELECT t2.name FROM table1 t1,table2 t2 WHERE t1.id = t2.trans_id) WHERE id IN (SELECT trans_id FROM table1 t1,table2 t2 WHERE t1.id = t2.trans_id)
它错误地填充了table1.
这是结果table1
| id | status | name | |----|----------|------| | 1 | refunded | cvb | | 2 | refunded | cvb |
我的要求是：
| id | status | name | |----|-----------|------| | 1 | refunded | cvb | | 2 | cancelled | asd |
我的查询有什么问题？我怎样才能实现它？
解决方法
我假设t2.trans_id是table2中的uniq或主键.如果没有,那么如果它返回多个结果,则更新查询将爆炸.在这种情况下,您需要使用WHERE应用更多过滤器,或者如果需要任何结果则使用TOP 1.
UPDATE table1
SET status = (SELECT t2.status FROM table2 t2 WHERE t2.trans_id = id),name = (SELECT t2.name FROM table2 t2 WHERE t2.trans_id = id)
WHERE id IN (SELECT trans_id FROM table2 t2 WHERE t2.trans_id= id)
