CODEWITHSUNDEEP
sqlsql-serversql-updatesyntax-error
Ben
Ben

Reputation: 609

SQL update syntax with subquery

I have this update statement:

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)

I get an error back but I'm not sure why

Incorrect syntax near 'qd'

Upvotes: 1

Views: 52

Answers (4)

Jit Patel
Jit Patel

Reputation: 28

Please try the below SQL:

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

Upvotes: 1

Maurice Reeves
Maurice Reeves

Reputation: 1583

SQL Server lets you do joined updates:

UPDATE 
    qd
SET 
    new_capvehicleid = cv.new_capvehicleid
FROM
    quotedetailextensionbase qd
    join vwCapidLookup cv on
        cv.new_capid = qd.new_capid 
        AND cv.new_captype = qd.new_captype
;

Does that make sense?

Upvotes: 1

Mureinik
Mureinik

Reputation: 311143

Seems like it would be easier to achieve this result with an update-join statement:

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

Upvotes: 2

user5507540
user5507540

Reputation: 1

Use MERGE: https://msdn.microsoft.com/en-us/library/bb510625.aspx. That is correct when you would update from another table.

Upvotes: 0

Related Questions