CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Praveen Kumar
Praveen Kumar

Reputation: 103

Update multiple columns from a sub query

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

Getting Error when i execute this query

ERROR: syntax error at or near "SELECT" LINE 3: SELECT key,'file

Upvotes: 1

Views: 391

Answers (2)

user330315
user330315

Reputation:

Support for updating tuples was introduced in Postgres 9.5, so you can't use that syntax with your version.

But as the second value is a constant I don't see a reason to use that syntax to begin with:

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

Note, that the sub-query might result in an error if there is more than one ATTACHMENT_VALUE for a document_id!

Upvotes: 1

Maxim
Maxim

Reputation: 253

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

or

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

Upvotes: 3

Related Questions