sql - 从子查询更新多个列

Question

UPDATE PINPOINT_SUPPLEMENT
            SET (ATTACHMENT_VALUE,ATTACHMENT_TYPE) = (
            SELECT key,'file'
            FROM PINPOINT_DOCUMENT
            WHERE PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE::integer = PINPOINT_DOCUMENT.DOCUMENT_ID
            )
WHERE ATTACHMENT_VALUE IS NULL

执行此查询时出错

错误：“SELECT”第 3 行或附近的语法错误：SELECT key,'file

标签： sqlpostgresqlpostgresql-9.4

Answer 1

update PINPOINT_SUPPLEMENT
set
    ATTACHMENT_VALUE = PINPOINT_DOCUMENT.key,
    ATTACHMENT_TYPE = 'file'
from PINPOINT_DOCUMENT
where
    PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE::integer = PINPOINT_DOCUMENT.DOCUMENT_ID
    and PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE IS NULL

或者

update PINPOINT_SUPPLEMENT
set
    (ATTACHMENT_VALUE,ATTACHMENT_TYPE) = (PINPOINT_DOCUMENT.key, 'file')
from PINPOINT_DOCUMENT
where
    PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE::integer = PINPOINT_DOCUMENT.DOCUMENT_ID
    and PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE IS NULL