MySQL UPDATE

-- 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: 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';

-- 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');