'How do I update multiple columns with a subquery in a single statement?

I am attempting to update a temp table from a source table:

    UPDATE #DETAIL
        SET EXCD_ID, CDOR_OR_AMT, CDOR_OR_VALUE
        (SELECT 
            CDID_ADDL_DATA_1, CDID_ADDL_DATA, CDID_VALUE_STRING
        FROM
            CMC_CDID_DATA CDID
        WHERE
            CDID.CLCL_ID = DTL.CLCL_ID AND
            CDID.CDML_SEQ_NO = DTL.CDML_SEQ_NO AND
            CDID_TYPE = 'NDC'
    )
    FROM #DETAIL DTL
    WHERE DTL.CDOR_OR_ID = 'XS'

Unfortunately it complains

Incorrect syntax near ',' (on the '(SELECT' line)
Incorrect syntax near 'FROM' (the second one)
sqlsybase


Solution 1:[1]

You have to make the update like this:

UPDATE #DETAIL
SET DTL.EXCD_ID = CDID.CDID_ADDL_DATA_1,
    DTL.CDOR_OR_AMT = CDID.CDID_ADDL_DATA
    DTL.CDOR_OR_VALUE = CDID.CDID_VALUE_STRING
FROM #DETAIL DTL
INNER JOIN (SELECT 
            CDID_ADDL_DATA_1, CDID_ADDL_DATA, CDID_VALUE_STRING
        FROM
            CMC_CDID_DATA ) CDID ON CDID.CLCL_ID = DTL.CLCL_ID AND
            CDID.CDML_SEQ_NO = DTL.CDML_SEQ_NO AND
            CDID_TYPE = 'NDC'
WHERE DTL.CDOR_OR_ID = 'XS'

Check THIS ARTICLE for more info!

Solution 2:[2]

I just tried this out and it worked (on Oracle)

update dstTable T
    set (T.field1, T.field2, T.field3) = 
           (select S.value1, S.value2, S.value3
            from srcTable S
             where S.key = T.Key);

This, unfortunately is Oracle specific syntax.

Caveat: Note that the update above has no where clause. It updates the entire table. If the subquery return no rows then the target fields are set to NULL. Also, it's an error if the subquery returns more than one row.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 aF.
Solution 2

Related Questions