Tuesday 14 February 2012

Update query with JOIN

One of my colleague just asked me about update query with JOIN but as he is new to SQL he tried to do exactly same as Select query and it was failing to execute. There is little difference when you do UPDATE query with JOIN. He was trying to execute below query and was not able to understand correct syntax of the query.

UPDATE Sales.SalesPerson AS sp
        INNER JOIN  Sales.SalesOrderHeader AS so
    ON sp.BusinessEntityID = so.SalesPersonID
       SET SalesYTD = SalesYTD + SubTotal
Where sp.SalesPersonID > 1

Below is correct syntax for running UPDATE query with JOIN:
UPDATE Sales.SalesPerson
        SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
    ON sp.SalesPersonID = so.SalesPersonID
Where sp.SalesPersonID > 1

No comments: