Updating Data
To modify existing data in a table, use the UPDATE statement. You can update one row or many rows, you can specify a single set of new values in the statement, or you can generate new values through a subquery.
Simple Updates
A simple UPDATE takes the following form:
UPDATEtable
SETcolumn
=value
,column
=value
. . . WHEREpredicates
In this form, predicates
identify one or more rows that you want to update. You can specify as many column
=
value
pairs as you like—one for each column you want to modify:
UPDATE upfall SET owner_id = 1 WHERE name = 'Munising Falls';
When you specify only one new value, you will usually want to update only one row, and your WHERE clause predicates should reference primary or unique key values to identify that row. Using expressions, you can write sensible UPDATEs that modify many rows:
UPDATE upfall SET datum = UPPER(datum), lat_lon = TRIM(UPPER(lat_lon));
This example also demonstrates the use of the comma to separate multiple-column updates in a SET clause.
Tip
In MySQL, if you are updating a self-referential foreign key or its related primary key, you should include an ORDER BY clause at the end of your update to control the order in which rows are updated. For more on this issue, see "Deleting in Order (MySQL)" and "Subquery Inserts,” earlier in the text.
New Values from a Subquery
You can also generate new values from a subquery. One way to do this is to write separate subqueries for each column that you are updating:
UPDATEtable
SETcolumn ...
Get SQL Pocket Guide, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.