PostgreSQL中基于关联表的SQL更新操作实现咨询

这其实是个很常见的关联更新场景，我来给你两种靠谱的解决方案，适配不同数据库的使用习惯：

方法1：用子查询定位用户ID #

这种写法逻辑最直观，先从Table1里拿到目标用户的ID，再用这个ID去更新Table2的对应记录：

UPDATE Table2
SET WIDGET_CONFIG = 'ghi'
WHERE USER_ID = (SELECT USER_ID FROM Table1 WHERE NAME = 'Test')
  AND WIDGET_ID = 2;

说明： #

• 子查询(SELECT USER_ID FROM Table1 WHERE NAME = 'Test')会先返回NAME为"Test"的用户ID（也就是你要的2）
• 外层的UPDATE会精准定位到Table2中USER_ID=2且WIDGET_ID=2的记录，把它的WIDGET_CONFIG改成"ghi"
• 如果Table1中可能存在多个NAME为"Test"的用户，把=换成IN即可，避免报错

方法2：用JOIN关联两张表更新 #

很多数据库（比如MySQL、PostgreSQL）支持通过JOIN的方式直接关联两张表进行更新，可读性更强：

UPDATE Table2 t2
JOIN Table1 t1 ON t2.USER_ID = t1.USER_ID
SET t2.WIDGET_CONFIG = 'ghi'
WHERE t1.NAME = 'Test'
  AND t2.WIDGET_ID = 2;

说明： #

• 我们给两张表起了别名t1和t2，让SQL更简洁
• 通过JOIN把Table2和Table1用USER_ID关联起来，直接筛选出Table1中NAME为"Test"的用户对应的Table2记录
• 最后指定更新条件是WIDGET_ID=2，完成更新

针对SQL Server的特殊写法 #

如果你用的是SQL Server，JOIN更新的语法略有不同，改成这样即可：

UPDATE t2
SET WIDGET_CONFIG = 'ghi'
FROM Table2 t2
INNER JOIN Table1 t1 ON t2.USER_ID = t1.USER_ID
WHERE t1.NAME = 'Test'
  AND t2.WIDGET_ID = 2;

验证小技巧 #

在执行更新前，建议先跑一下对应的SELECT语句，确认要更新的记录是否正确，避免误操作：

SELECT t2.*
FROM Table2 t2
JOIN Table1 t1 ON t2.USER_ID = t1.USER_ID
WHERE t1.NAME = 'Test'
  AND t2.WIDGET_ID = 2;

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