UPDATE Multiple Columns
In this page:
Introduction to Multiple Column Updates
You can update more than one column in a single UPDATE statement, such as changing a product's price and stock together. Separate each column-value pair with a comma. This is faster and cleaner than running multiple separate queries against the same rows.
Example: Introduction to Multiple Column Updates
UPDATE products SET price = 29.99, stock = 100 WHERE id = 1;Updating Strings and Numbers Together
MySQL allows you to update different data types at the same time within one statement. You can change text fields, numbers, and dates in one command. Just make sure the new values match the column types, or MySQL will attempt an implicit (and sometimes lossy) conversion.
Example: Updating Strings and Numbers Together
UPDATE users SET name = 'Amit Kumar', age = 31, signup_date = '2024-01-01' WHERE id = 1;Using Expressions on Multiple Columns
You can use mathematical expressions on multiple columns simultaneously, such as recalculating a total and a tax amount together. Each column will be updated according to its defined formula. The updates happen safely in a single atomic step, so no other query sees a half-updated row.
Example: Using Expressions on Multiple Columns
UPDATE orders SET total = total + 10, tax = tax + 1 WHERE id = 1;Conditional Logic with CASE
You can use CASE statements inside your multi-column updates to apply different logic per row. This lets you apply different rules to different columns based on conditions, such as giving a discount tier based on order total. It is highly efficient for complex business logic that would otherwise need several separate UPDATE statements.
Example: Conditional Logic with CASE
UPDATE products
SET category = CASE WHEN price > 100 THEN 'premium' ELSE 'standard' END;Best Practices for Safe Multiple Updates
Always verify your WHERE clause before updating multiple fields, since a mistake here affects every column you're setting at once. Running a SELECT query with the same WHERE clause first is a safe practice. This ensures you only modify the intended rows before committing to the change.
Example: Best Practices for Safe Multiple Updates
SELECT * FROM products WHERE id = 1; -- preview before updating
UPDATE products SET price = 29.99, stock = 100 WHERE id = 1;Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: