Changes to Table Rows

To update one or more rows, use the UPDATE statement. The new data can be a constant or an expression that you specify, or data pulled from other tables.

As in all data modification statements, you can change the data in only one table or view at a time.

If an UPDATE statement violates an integrity constraint, the update does not take place and an error message appears. For example, if one of the values being added is the wrong data type, or if it violates a constraint defined for one of the columns or data types involved, the update does not take place.

A simplified version of the syntax is:

UPDATE table-name 
SET column_name = expression
WHERE search-condition

Examples

If the company Newton Ent. (in the Customers table of the demo database) is taken over by Einstein, Inc., you can update the name of the company using a statement such as:

UPDATE Customers
SET company_name = 'Einstein, Inc.'
WHERE company_name = 'Newton Ent.'

You can use any condition in the WHERE clause. If you are unsure how the company name was entered, try updating any company called Newton, with a statement such as the following:

UPDATE Customers
SET company_name = 'Einstein, Inc.'
WHERE company_name LIKE 'Newton%'

The search condition need not refer to the column being updated. The company ID for Newton Ent. is 109. As the ID value is the primary key for the table, you could be sure of updating the correct row using:

UPDATE Customers
SET company_name = 'Einstein, Inc.'
WHERE id = 109

The SET Clause

The SET clause specifies the columns to be updated, and their new values. The WHERE clause determines the rows to be updated. If you do not use a WHERE clause, the specified columns of all rows are updated with the values in the SET clause.

You can provide any expression of the correct data type in the SET clause.

The WHERE Clause

The WHERE clause specifies the rows to be updated. For example:, the following statement replaces "One Size Fits All Tee Shirt" with " Extra Large Tee Shirt".

UPDATE Products
SET size  = 'Extra Large'
WHERE name = 'Tee Shirt' 
   AND size = 'One Size Fits All'

The FROM Clause

You can use a FROM clause to pull data from one or more tables into the table you are updating. You can also employ a FROM clause to use selection criteria against another table to control which rows are updated.