PostgreSQL使用Join（含自连接）更新表时出现列不存在错误求助

我需要通过自连接更新表，把同表中某行的列值复制到另一行的对应列。示例数据如下：

SERVICE ID code       value
123        QUANTITY1  2
123        QUANTITY2  null

期望将QUANTITY2的value更新为2。我编写的SQL语句如下：

UPDATE "X"."ORDER_PRODUCT" op
set op."PROPERTY_VALUE"= op2."PROPERTY_VALUE" 
from "X"."ORDER_PRODUCT" op2  cross join 
"X"."ORDER_PROJECT" op3
where op."SERVICE_ID"= op2."SERVICE_ID" and op."ORDER_ID" =op3."ID" 
and op."PRODUCT_CODE" ='Tell_Datacenter_Co-location_Alan' and op2."PROPERTY_CODE" ='Quantity' 
and op."PROPERTY_CODE"='Tell_Quantity' and op."PROPERTY_VALUE" is null
and op3."PROJECT_STATUS" not in ('ProjectWF.Cancel','ProjectWF.Complete') 

执行时出现错误：

SQL Error [42703]: ERROR: 关系"ORDER_PRODUCT"的列"op"不存在
位置：48

错误原因 #

PostgreSQL的UPDATE语法规则中，SET子句里不能使用目标表的别名来引用列，直接写列名即可——因为UPDATE子句已经指定了目标表，数据库能明确识别要更新的列。原语句中set op."PROPERTY_VALUE"的写法违反了这个规则，导致报错。另外原语句中的cross join可以改为inner join，明确关联条件，避免生成不必要的笛卡尔积，提升查询效率。

修正后的SQL语句 #

UPDATE "X"."ORDER_PRODUCT" op
SET "PROPERTY_VALUE" = op2."PROPERTY_VALUE"
FROM "X"."ORDER_PRODUCT" op2
INNER JOIN "X"."ORDER_PROJECT" op3 ON op."ORDER_ID" = op3."ID"
WHERE op."SERVICE_ID" = op2."SERVICE_ID"
  AND op."PRODUCT_CODE" = 'Tell_Datacenter_Co-location_Alan'
  AND op2."PROPERTY_CODE" = 'Quantity'
  AND op."PROPERTY_CODE" = 'Tell_Quantity'
  AND op."PROPERTY_VALUE" IS NULL
  AND op3."PROJECT_STATUS" NOT IN ('ProjectWF.Cancel', 'ProjectWF.Complete')

关键修改点 #

• 移除了SET子句中目标表的别名op，直接写"PROPERTY_VALUE"
• 将cross join改为inner join，并把op."ORDER_ID" = op3."ID"移到ON子句中，明确关联逻辑
• 保留原有的过滤条件，确保只更新符合业务要求的行

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