CODEWITHSUNDEEP
sqlsybase
Jack B Nimble
Jack B Nimble

Reputation: 5087

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)

Upvotes: 5

Views: 22408

Answers (3)

Darrel Lee
Darrel Lee

Reputation: 2470

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.

Upvotes: 0

Jack B Nimble
Jack B Nimble

Reputation: 5087

After much trial and error I pooled some help at work and we came up with this:

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

Which sybase seems to accept.

Upvotes: 4

aF.
aF.

Reputation: 66697

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!

Upvotes: 3

Related Questions