如何在单个语句子查询更新多个列？(How do I update multiple columns

我试图从一个源表更新一个临时表：

    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'

不幸的是，抱怨

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

Answer 1:

多试错后，我在收集一些工作帮助我们想出了这个：

    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'

这SYBASE似乎接受。

Answer 2:

你必须作出这样的更新：

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'

---

查看这篇文章的详细信息！

文章来源: How do I update multiple columns with a subquery in a single statement?