UPDATE Statement
In this page:
Introduction to UPDATE
The UPDATE statement lets you change existing records in a table, such as correcting a customer's shipping address. It helps you modify values that are already saved without deleting and re-inserting the row. Always use a WHERE clause to target specific rows, or every row in the table will change.
Example: Introduction to UPDATE
UPDATE customers SET address = '123 New St' WHERE id = 1;The Importance of the WHERE Clause
The WHERE clause specifies which records should be updated, exactly like it does in a SELECT. If you omit the WHERE clause, all records in the table will be updated. This can accidentally overwrite all your database data, so it's worth running the same WHERE as a SELECT first to preview the affected rows.
Example: The Importance of the WHERE Clause
UPDATE customers SET status = 'inactive'; -- updates ALL rows, no WHERE clauseUpdating with Expressions
You can update a column using its current value, such as UPDATE products SET stock = stock - 1. This is useful for incrementing numbers or appending text. MySQL calculates the expression for each row dynamically, using that row's own existing value.
Example: Updating with Expressions
UPDATE products SET stock = stock - 1 WHERE id = 1;Updating with NULL Values
You can clear a column's value by setting it to NULL, such as removing a scheduled deletion date once it's cancelled. This works only if the column is allowed to hold NULL values (not defined as NOT NULL). It is useful for resetting fields back to an empty, unknown state.
Example: Updating with NULL Values
UPDATE tasks SET scheduled_deletion = NULL WHERE id = 1;Limiting the Update
You can use the LIMIT clause to restrict the number of updated rows, capping a batch update at a fixed size. This is helpful for updating records in smaller batches. It prevents performance issues and long table locks on very large tables.
Example: Limiting the Update
UPDATE products SET discontinued = 1 LIMIT 10;Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: