使用CTE交换表两列数据报no such column错误，求正确SQL写法

你遇到的报错是因为UPDATE语句仅定义了CTE但没有将其与目标表SalComm关联，数据库无法识别cte.comm1等字段归属。

基于CTE的正确写法 #

适用MySQL、PostgreSQL、SQLite 3.33及以上版本（支持UPDATE JOIN语法） #

WITH CTE AS (
    SELECT ID AS id1, sal AS sal1, comm AS comm1 FROM SalComm
)
UPDATE SalComm
JOIN CTE ON SalComm.ID = CTE.id1
SET sal = CTE.comm1, comm = CTE.sal1;

适用SQL Server、Oracle等支持UPDATE FROM语法的数据库 #

WITH CTE AS (
    SELECT ID AS id1, sal AS sal1, comm AS comm1 FROM SalComm
)
UPDATE SalComm
SET sal = CTE.comm1, comm = CTE.sal1
FROM SalComm
INNER JOIN CTE ON SalComm.ID = CTE.id1;

更简洁的无CTE写法 #

如果你不需要强制使用CTE，标准SQL下可以直接用下面的语句实现两列值交换。SQL的UPDATE机制会先计算所有赋值表达式右侧的原始值再统一写入，不会出现值覆盖的问题：

UPDATE SalComm
SET sal = comm, comm = sal;

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