SQLite同表子查询更新refer列：现有可行语句是否正确？

首先，咱们先聊聊你第一条语句为啥失效：

update tab1 set refer = (select col2 from tab1 where col1 = refer) where col1 = 2

这里的核心问题是字段引用歧义——子查询里的refer没有明确指定是来自外层要更新的tab1行，SQLite会默认把它当成子查询内部tab1表的refer字段，相当于自己跟自己绕圈，自然查不出正确的匹配结果，甚至可能返回空值导致更新失败。

再看你写的第二条语句：

update tab1 set refer = (select tem1.col2 from tab1 tem1, tab1 tem2 where tem1.col1 = tem2.refer and tem2.col1=2) where col1 = 2

这条语句能运行，但逻辑上有明显的坑：它的子查询是找出所有tem2.col1=2的行对应的tem1.col2，如果有多个符合条件的行，子查询会返回多行结果，这时候SQLite会直接报错（因为单个字段不能接收多个值）；就算只有一个结果，所有col1=2的行都会被设成同一个col2值，这显然不是你要的“每一行的refer对应自身匹配的col2”的逻辑。

推荐的正确写法 #

这里给你两种可靠的实现方式，适配不同版本的SQLite：

方式1：带别名的相关子查询（兼容所有SQLite版本）

明确用别名区分内外表，确保子查询引用的是外层要更新的行的refer：

UPDATE tab1
SET refer = (
    SELECT t2.col2
    FROM tab1 t2
    WHERE t2.col1 = tab1.refer  -- 这里的tab1.refer就是外层要更新的那一行的refer值
)
WHERE col1 = 2;

如果想避免把refer更新为NULL（当没有匹配的col1时），可以加个EXISTS条件过滤：

UPDATE tab1
SET refer = (
    SELECT t2.col2
    FROM tab1 t2
    WHERE t2.col1 = tab1.refer
)
WHERE col1 = 2
AND EXISTS (
    SELECT 1 FROM tab1 t2 WHERE t2.col1 = tab1.refer
);

方式2：使用UPDATE ... FROM语法（SQLite 3.33.0及以上支持）

这种写法逻辑更直观，把要更新的表和匹配表做关联：

UPDATE tab1 t1
SET refer = t2.col2
FROM tab1 t2
WHERE t1.col1 = 2
AND t2.col1 = t1.refer;

它会自动只更新有匹配的行，不会产生NULL值，代码也更简洁。

这两种写法都能精准实现你的需求：把col1=2的每一行的refer，替换成同表中col1等于该行原refer值的col2内容。

内容的提问来源于stack exchange，提问作者Suresh