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.
UPDATE and Security Use of the UPDATE statement can be restricted and controlled. More on this in Chapter 28, "Managing Security."
The UPDATE statement is very easy to usesome would say too easy. The basic format of an UPDATE statement is made up of three parts:
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 = 'firstname.lastname@example.org' 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 = 'email@example.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 addressdefinitely not the desired effect.
UPDATE customers SET cust_name = 'The Fudds', cust_email = 'firstname.lastname@example.org' 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.
Using Subqueries in an UPDATE Statement Subqueries may be used in UPDATE statements, enabling you to update columns with data retrieved with a SELECT statement. Refer to Chapter 14, "Working with Subqueries," for more information on subqueries and their uses.
The IGNORE Keyword 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;