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)
Advertisement
Answer
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.