时间戳

首页 > 解决方案 > 在 PostgreSQL 中使用子查询更新不起作用

postgresql - 在 PostgreSQL 中使用子查询更新不起作用

问题描述

我正在使用 Postgresql 9.3，这个 SQL 不起作用：

UPDATE revision_table
SET (code, uuid, converted_entity1uuid, converted_entity1code) = 
         (SELECT convertedT.code, convertedT.uuid, convertedT.uuid, convertedT.code
          FROM currency convertedT 
          INNER JOIN currency_conversion_aud conversionT ON conversionT.currency1_id = convertedT.id
          WHERE conversionT.id = 125
          ORDER BY rev DESC LIMIT 1), 
    (converted_entity2uuid, converted_entity2code) = 
         (SELECT convertedT.uuid, convertedT.code 
          FROM currency convertedT 
          INNER JOIN currency_conversion_aud conversionT ON conversionT.currency2_id = convertedT.id
          WHERE conversionT.id = 125
          ORDER BY rev DESC LIMIT 1) 
WHERE id = 12;

错误：

错误：“SELECT”
LIGNE 3 处或附近的语法错误：（SELECT convertT.code，convertedT.uuid ...

但是如果我在 postgres 9.6 中运行这个 SQL，它就可以正常工作：

UPDATE revision_table
SET (code, uuid, converted_entity1uuid, converted_entity1code) = 
         (SELECT convertedT.code, convertedT.uuid, convertedT.uuid, convertedT.code
          FROM currency convertedT 
          INNER JOIN currency_conversion_aud conversionT ON conversionT.currency1_id = convertedT.id
          WHERE conversionT.id = 12
          ORDER BY rev DESC LIMIT 1), 
    (converted_entity2uuid, converted_entity2code) = 
         (SELECT convertedT.uuid, convertedT.code 
          FROM currency convertedT 
          INNER JOIN currency_conversion_aud conversionT ON conversionT.currency2_id = convertedT.id
          WHERE conversionT.id = 12 
          ORDER BY rev DESC LIMIT 1) 
WHERE id = 12;

UPDATE 0
Temps : 75,359 ms

由于生产在 9.3 中，我如何转换为与 9.3 兼容？

感谢帮助

标签： postgresqlsql-updatesubquerypostgresql-9.3

解决方案

终于解决了：

UPDATE 
  revision_table
SET
  code = sub1.code, 
  uuid = sub1.uuid,
  converted_entity1uuid = sub1.uuid,
  converted_entity1code = sub1.code,
  converted_entity2uuid=sub2.uuid,
  converted_entity2code=sub2.code
FROM (
  SELECT convertedT.code, convertedT.uuid
  FROM currency convertedT 
  INNER JOIN currency_conversion_aud conversionT
    ON conversionT.currency1_id = convertedT.id
  WHERE conversionT.id = 12
  ORDER BY rev DESC LIMIT 1
) sub1,
(
  SELECT convertedT.uuid, convertedT.code 
  FROM currency convertedT 
  INNER JOIN currency_conversion_aud conversionT
    ON conversionT.currency2_id = convertedT.id
  WHERE conversionT.id = 12 
  ORDER BY rev DESC LIMIT 1
)  sub2
WHERE id = 12;

推荐阅读