SQL UPDATE Statement

Friday, October 28, 2011

tags SQL

The UPDATE statement is used to update existing records in a table.  It is very important to use the WHERE clause in combination with the UPDATE statement.  One or more fields can be referenced.  If you OMIT the WHERE clause, all records in the table will be updated!


UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value


UPDATE Customers
SET State='Georgia', City='Atlanta'
WHERE LastName='Smith' AND FirstName='John'

Advanced Example using SELECT in combination with UPDATE

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the UPDATE statement, you can use the EXISTS clause.

UPDATE Marketing
SET MCustName = ( SELECT Customers.Name
FROM Customers
WHERE Customers.CustID = Marketing.CustID)
( SELECT Customers.Name
FROM Customers
WHERE Customers.CustID = Marketing.CustID)

Whenever a Marketing.CustID is matched with a Customers.CustID value, the MCustName would be updated with the customer's name from the Customers table.

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook