sql - 带子查询的SQL更新语法

Question

我有这个update语句:

UPDATE quotedetailextensionbase qd
SET qd.new_capvehicleid = (SELECT cv.new_capvehicleid
                           FROM vwCapidLookup cv
                           WHERE cv.new_capid = qd.new_capid 
                             AND cv.new_captype = qd.new_captype)

我回来了一个错误，但我不确定为什么

Incorrect syntax near 'qd'

Answer 1

似乎使用update-join语句更容易实现此结果:

UPDATE qd
SET    new_capvehicleid = cv.new_capvehicleid
FROM   quotedetailextensionbase qd
JOIN   (SELECT new_capvehicleid
        FROM   vwCapidLookup) cv ON cv.new_capid = qd.new_capid AND
                                    cv.new_captype = qd.new_captype