Using UPDATE

We can use the UPDATE SQL statement to change rows already stored in the database. For example, imagine that one of our employees changes jobs:



update employee
set job='DBA'
where employeeID='6651';

This statement changes the value of the job column for employee number 6651.

The general form of the UPDATE statement from the MySQL manual is as follows:



UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT rows]

or

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

The UPDATE statement is similar in many respects to the DELETE statement.

We can use an optional WHERE clause to update particular rows or leave it off to update all rows. Again, you can fall into the trap of forgetting to specify a WHERE clause—I remember one project when a foolish colleague typed something along these lines:



update user
set password='test';

This again highlights the usefulness of the --i-am-a-dummy mysql option, particularly if you are forced to work with dummies.

The second version of the UPDATE statement listed previously is a multi-table update. This works similarly to the multi-table deletes we looked at before. Note that only the columns you specifically list in the SET clause will be updated.

We have seen all the other clauses of the UPDATE statement before. The LOW_PRIORITY and IGNORE clauses work the same way as they do in INSERT. The ORDER BY and LIMIT clauses work the same way they do in DELETE.