SQLite更新查询

本文介绍了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'