MySQL UPDATE Statement

MySQL UPDATE Statement: Modifying Existing Rows

The MySQL UPDATE statement modifies existing values in one or more columns of a table. You specify which columns to change with SET and restrict which rows are touched using a WHERE clause.

Basic Syntax

Always verify your target rows with a SELECT query before executing an UPDATE:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Omitting the WHERE clause updates every single row in the table, which can corrupt production data in milliseconds.

Practical SQL Example

This query increases the price of a specific item in demo_products and reduces its stock count inside a safe WHERE id = ... filter.

UPDATE demo_products
SET price = 94.99,
    stock_qty = stock_qty - 1
WHERE id = 8
  AND stock_qty > 0;

-- Confirm updated row
SELECT id, product_name, price, stock_qty
FROM demo_products
WHERE id = 8;

Safety Practices for Production Updates

• Test with SELECT First: Run SELECT * FROM demo_products WHERE ... with your exact WHERE predicate to confirm how many rows match before modifying anything.
• Wrap Bulk Edits in a Transaction: Run START TRANSACTION; UPDATE ...; SELECT ...; so you can inspect the result and execute ROLLBACK; if anything looks wrong before running COMMIT;.
• Prevent Race Conditions: Use relative expressions like stock_qty = stock_qty - 1 WHERE stock_qty > 0 instead of overwriting with a stale value read earlier in application code.
• Limit Batch Size: Update large tables in primary-key chunks so InnoDB row locks clear quickly.

Frequently Asked Questions

What happens if an UPDATE statement matches zero rows?

MySQL succeeds without error and reports 0 rows matched, 0 changed.

Can I update a table based on values in another table?

Yes, MySQL supports UPDATE demo_orders o INNER JOIN demo_customers c ON o.customer_id = c.id SET ... WHERE ....

Can I use LIMIT with UPDATE in MySQL?

Yes, adding ORDER BY id LIMIT 1000 lets you update millions of rows in small batches without locking the table.