-
To update specific rows in a table
-
To update all rows in a table
Let's take a look at each of these uses.
Don't Omit the WHERE Clause Special care must be exercised when using UPDATE because it is all too easy to mistakenly update every row in your table. Please read this entire section on UPDATE before using this statement.
The UPDATE statement is very easy to use some would say too easy. The basic format of an UPDATE statement is made up of three parts:
-
The table to be updated
-
The column names and their new values
-
The filter condition that determines which rows should be updated
Let's take a look at a simple example. Customer 10005 now has an email address, and so his record needs updating. The following statement performs this update:
UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
The UPDATE statement always begins with the name of the table being updated. In this example, it is the customers table. The SET command is then used to assign the new value to a column. As used here, the SET clause sets the cust_email column to the specified value:
SET cust_email = 'elmer@fudd.com'
The UPDATE statement finishes with a WHERE clause that tells MySQL which row to update. Without a WHERE clause, MySQL would update all the rows in the customers table with this new email address definitely not the desired effect.
Updating multiple columns requires a slightly different syntax:
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
When updating multiple columns, only a single SET command is used, and each column = value pair is separated by a comma. (No comma is specified after the last column.) In this example, columns cust_name and cust_email will both be updated for customer 10005.
Subqueries may be used in UPDATE statements, enabling you to update columns with data retrieved with a SELECT statement.
If your UPDATE statement updates multiple rows and an error occurs while updating one or more of those rows, the entire UPDATE operation is cancelled (and any rows updated before the error occurred are restored to their original values). To continue processing updates, even if an error occurs, use the IGNORE keyword, like this:
UPDATE IGNORE customers ...
To delete a column's value, you can set it to NULL (assuming the table is defined to allow NULL values). You can do this as follows:
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;
Here the NULL keyword is used to save no value to the cust_email column.