（转）SQL: UPDATE Statement

The UPDATE statement allows you to update a single record or multiple records in a table.

The syntax the UPDATE statement is:

UPDATE table
SET column = expression
WHERE predicates;

Example #1 - Simple example

Let's take a look at a very simple example.

UPDATE supplier
SET name = 'HP'
WHERE name = 'IBM';

This statement would update all supplier names in the supplier table from IBM to HP.

Example #2 - More complex example

You can also perform more complicated updates.

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.

For example:

UPDATE supplier
SET supplier_name =	( SELECT customer.name FROM customers WHERE customers.customer_id = supplier.supplier_id)
WHERE EXISTS   ( SELECT customer.name     FROM customers     WHERE customers.customer_id = supplier.supplier_id);

Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer name from the customers table.