MySQL UPDATE Modify Rows

UPDATE ... SET ... WHERE

A missing or incorrect WHERE clause can change an entire table; select the target keys first and keep a recoverable path.

The UPDATE statement modifies existing rows in a table. Always include a WHERE clause - without it, every row in the table will be updated. A good habit is to run the equivalent SELECT first to preview which rows will be affected.

Basic UPDATE and Updating Multiple Columns

-- Update a single column
UPDATE customers
SET email = 'alice.new@example.com'
WHERE customer_id = 1;

-- Update multiple columns at once
UPDATE customers
SET
    first_name = 'Alice',
    last_name  = 'Johnson-Smith',
    phone      = '555-0199',
    active     = 1
WHERE customer_id = 1;

-- Update using an expression (apply 10% discount to Electronics)
UPDATE products
SET price = price * 0.90
WHERE category = 'Electronics';

-- Increment a value
UPDATE products
SET stock = stock + 100
WHERE product_id = 2;

-- Update with LIMIT (update at most N rows)
UPDATE products
SET stock = 0
WHERE stock < 5
LIMIT 10;

UPDATE with JOIN

MySQL supports updating a table based on data from another table using a JOIN in the UPDATE statement. This is more efficient than a correlated subquery for large datasets.

UPDATE with JOIN and Subquery

-- UPDATE with JOIN: mark customers as VIP if they have 3+ orders
UPDATE customers c
INNER JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
    HAVING order_count >= 3
) AS order_summary ON c.customer_id = order_summary.customer_id
SET c.active = 1;

-- UPDATE with subquery: set order total from order_items sum
UPDATE orders o
SET o.total = (
    SELECT SUM(quantity * unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.order_id
)
WHERE o.status = 'pending';

Safe Update Mode

MySQL Workbench enables safe update mode by default (sql_safe_updates = 1), which prevents UPDATE and DELETE statements that don't use a key column in the WHERE clause. This protects against accidental mass updates.

Safe Update Mode - SQL Example

-- Check current safe update mode setting
SHOW VARIABLES LIKE 'sql_safe_updates';

-- Temporarily disable safe update mode for the current session
SET sql_safe_updates = 0;

-- Now you can run updates without a key in WHERE
UPDATE products SET stock = 0 WHERE category = 'Electronics';

-- Re-enable safe update mode
SET sql_safe_updates = 1;

-- Best practice: always use the primary key in WHERE when possible
UPDATE products SET price = 49.99 WHERE product_id = 3;

-- Or use a key-based subquery to satisfy safe mode
UPDATE products
SET stock = 0
WHERE product_id IN (SELECT product_id FROM products WHERE category = 'Electronics');

Changing MySQL rows safely with UPDATE

UPDATE modifies existing rows, so it deserves more caution than a read query. The SET clause defines the new values, and WHERE decides which rows are affected. Without WHERE, every row in the table can be changed, which is one of the most serious beginner database mistakes.

A safe update workflow is to first run a SELECT with the same WHERE condition, confirm the rows, then run UPDATE inside a transaction when possible. For important data, keep a backup or audit trail. In production, UPDATE statements should be narrow, reviewed, and reversible when the business risk is high.

Preview affected rows with SELECT first.
Always check the WHERE condition.
Use transactions for risky multi-row updates.
Check affected row count after running the statement.

Update only products in one category

START TRANSACTION;

UPDATE products
SET price = price * 1.10
WHERE category = 'books';

COMMIT;

Before you move on