Professional SQL Server™ 2005 Programming

3.3. Changing What You've Got with the UPDATE Statement

The UPDATE statement, like most SQL statements, does pretty much what it sounds like it does— it updates existing data. The structure is a little bit different from a SELECT, though you'll notice definite similarities. Let's look at the syntax:

UPDATE <table name>
SET <column> = <value> [,<column> = <value>]
[FROM <source table(s)>]
[WHERE <restrictive condition>]

An UPDATE can be created from multiple tables, but can affect only one table. What do I meanby that? Well, we can build a condition, or retrieve values from any number of different tables (via a join), but only one table at a time can be the subject of the update action. For now, we'll look at a simple update.

Let's start off by looking at our old friend Jo Brown (you may remember her from our look at joins earlier in the chapter). It seems that Jo has recently gotten married, and we need to make sure that her data is accurate. Let's run a query to look at one row of data:

SELECT e.EmployeeID,
    e.MaritalStatus,
    ce.FirstName,
    ce.LastName
FROM HumanResources.Employee e
JOIN Person.Contact ce
  ON e.ContactID = ce.ContactID
WHERE ce.FirstName = 'Jo'
  AND ce.LastName = 'Brown';

Which returns the following:

EmployeeID  MaritalStatus FirstName                LastName
----------- ------------- ------------------------ --------------------------------
16          S             Jo                       Brown

(1 row(s) affected)

Let's update the MaritalStatus value to the more proper "M":

UPDATE e SET MaritalStatus = 'M' FROM HumanResources.Employee ...