My Technical Notes

Wednesday, 30 January 2013

Using an UPDATE statement in a join

In SQL, you can perform an update on a single table:


UPDATE Country
SET Deficit = 0
WHERE Name = 'Nigeria'

The above query can be re-written to include a variable / alias for the Country:


UPDATE c
SET c.Deficit = 0
FROM Country c
WHERE c.Name = 'Nigeria'

We need variables when we intend to join tables and thereafter do an update. For example:


UPDATE c
SET c.Deficit = 0
FROM 
    Country c 
    INNER JOIN Continent cont ON cont.ContinentID = c.ContinentID
WHERE cont.Name = 'Africa'

No comments: