PostgreSQL中DO UPDATE SET子句用子查询更新字段报错求解

问题描述 #

我编写了如下SQL查询语句：

INSERT INTO table1 (
  col1,
  col2,
  col3
)
VALUES (
  %s,
  (SELECT id FROM table2 WHERE name = %s),
  %s, -- 此处存在多余逗号，属于语法错误
)
ON CONFLICT (fk_id)
DO UPDATE SET
  col1 = %s,
  col2 = (SELECT id FROM table2 WHERE name = %s), -- 触发“String literal error”的语句
  col3 = %s
WHERE EXISTS (
  SELECT 1 FROM table1 WHERE fk_id = %s
)
RETURNING id;

其中标注的col2 = (SELECT id FROM table2 WHERE name = %s)语句触发了“String literal error”错误，移除该语句后查询可正常执行。请问是否能够在DO UPDATE SET子句中通过查询另一表获取值来更新字段？

解答 #

完全可以在DO UPDATE SET子句中通过子查询从另一表获取值来更新字段，这种写法在支持ON CONFLICT语法的数据库（比如PostgreSQL）中是合法的。

你的错误并非来自子查询本身，大概率是以下原因导致：

1. VALUES子句的语法错误：你在VALUES部分最后一个%s后面多了一个逗号，这属于语法错误，可能干扰了后续语句的解析，移除col2的更新语句后可能顺带修正了这个问题（或数据库忽略了该错误）。
2. 参数绑定问题：检查参数的数量和类型是否匹配。整个语句中共有7个%s占位符，需要确保传入的参数数量刚好是7个，且每个参数的类型与对应字段匹配（比如table2.name对应的参数是否为字符串类型）。
3. 冗余的WHERE EXISTS条件：ON CONFLICT已经定位到冲突的行，DO UPDATE只会作用于该行，不需要额外通过WHERE EXISTS查询table1，这个条件可以直接移除。

修正后的示例代码 #

INSERT INTO table1 (
  col1,
  col2,
  col3
)
VALUES (
  %s,
  (SELECT id FROM table2 WHERE name = %s),
  %s -- 移除多余的逗号
)
ON CONFLICT (fk_id)
DO UPDATE SET
  col1 = %s,
  col2 = (SELECT id FROM table2 WHERE name = %s),
  col3 = %s
RETURNING id;

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