UPDATE - MySQL
Overview
The UPDATE command modifies existing rows in a specified table by setting one or more column values. It is commonly used to change data, correct errors, and update outdated information.
Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Required Arguments:
- table_name: The name of the table to update.
- SET: Followed by a comma-separated list of column-value pairs to update.
- WHERE: Specifies the condition(s) to filter the rows to be updated.
Optional Arguments:
- LIMIT: Restricts the number of rows to be updated.
- ORDER BY: Sorts the rows to be updated before applying the changes.
Options/Flags
- IGNORE: Skips rows that cause errors during the update.
- RETURNED_UPDATED_ROWS: Returns the number of rows that were successfully updated.
- DELIMITER: Customizes the delimiter used in the SET clause.
Examples
Example 1: Update a single column:
UPDATE customers SET email = 'new_email@example.com' WHERE id = 1;
Example 2: Update multiple columns:
UPDATE products SET price = 100, stock = 50 WHERE name = 'product1';
Example 3: Update with a subquery:
UPDATE orders SET status = (SELECT status FROM order_statuses WHERE order_type = 'online');
Common Issues
- No rows updated: Verify that the WHERE condition matches existing rows.
- Error in update expression: Ensure the data type of the new values matches the column definitions.
- Concurrency errors: Use transactions to handle data consistency when multiple updates are performed concurrently.
Integration
The UPDATE command can be combined with other commands for advanced tasks:
- SELECT … WHERE EXISTS(… UPDATE …): Check if an update was successful before performing subsequent actions.
- WITH ROLLUP/CUBE: Summarize data and update aggregate rows.