SQLite更新查询-具有别名的子查询不起作用 [英] SQLite update query - subquery with aliases doesn't work
本文介绍了SQLite更新查询-具有别名的子查询不起作用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要更新一个SQLite表。
I need to update a SQLite table.
表格如下:
ID | Address | CallNumber | RefID
-----+--------------------+-------------------------------------------
ef78 | library | 2002/13 | 100002
no56 | Lit | 0189 | 100003
rs90 | temp | | 100003
对于地址= Lit的每一列,都有地址='temp'的列相同的RefID。
现在,我需要使用相同的RefID从列中使用值 CallNumber更新每个Address = temp。
For every column with Address = "Lit" there is a column Address = 'temp' with the same RefID. Now I need to update each Address = "temp" with the value "CallNumber" from the column with the same RefID.
更新后的表应类似于:
ID | Address | CallNumber | RefID
-----+--------------------+-------------------------------------------
ef78 | library | 2002/13 | 100002
no56 | Lit | 0189 | 100003
rs90 | 0189 | | 100003
我尝试了此操作:
UPDATE Location
SET address = foo.callnumber
FROM (select RefID, CallNumber FROM Location) foo
WHERE foo.RefID=Location.RefID AND Location.Address = 'temp';
但是我所得到的只是 from附近的语法错误。
But all I got is a syntax error near "from".
任何线索?
推荐答案
UPDATE
命令没有 FROM
子句。
使用相关子查询:
UPDATE Location
SET Address = (SELECT CallNumber
FROM Location L2
WHERE L2.RefID = Location.RefID
AND L2.Address = 'Lit')
WHERE Address = 'temp'
这篇关于SQLite更新查询-具有别名的子查询不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文