UPDATE Statement Examples

-- ========================================
-- BASIC UPDATE STATEMENTS
-- ========================================

-- Update single column
UPDATE users 
SET last_login = NOW()
WHERE user_id = 123;

-- Update multiple columns
UPDATE products 
SET 
    price = 899.99,
    stock_quantity = 100,
    updated_at = NOW()
WHERE product_id = 456;

-- Update with arithmetic
UPDATE products 
SET 
    price = price * 1.10,  -- 10% price increase
    stock_quantity = stock_quantity - 5
WHERE category_id = 3;

-- Update with CASE statement
UPDATE orders 
SET status = CASE
    WHEN total_amount < 100 THEN 'small_order'
    WHEN total_amount < 500 THEN 'medium_order'
    ELSE 'large_order'
END
WHERE status IS NULL;

-- ========================================
-- UPDATE WITH CONDITIONS
-- ========================================

-- Complex WHERE conditions
UPDATE users 
SET 
    account_status = 'inactive',
    deactivated_at = NOW()
WHERE 
    last_login < DATE_SUB(NOW(), INTERVAL 6 MONTH)
    AND account_status = 'active'
    AND user_type != 'admin';

-- Update with subquery in WHERE
UPDATE products 
SET is_featured = TRUE
WHERE product_id IN (
    SELECT product_id 
    FROM order_items 
    GROUP BY product_id 
    ORDER BY SUM(quantity) DESC 
    LIMIT 10
);

-- Update with EXISTS
UPDATE customers c
SET is_vip = TRUE
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id 
    GROUP BY o.customer_id 
    HAVING SUM(total_amount) > 10000
);

-- ========================================
-- UPDATE WITH JOINS
-- ========================================

-- Update with INNER JOIN
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
SET 
    o.customer_type = c.customer_type,
    o.customer_region = c.region
WHERE o.customer_type IS NULL;

-- Update with LEFT JOIN
UPDATE products p
LEFT JOIN product_reviews pr ON p.product_id = pr.product_id
SET p.average_rating = (
    SELECT AVG(rating) 
    FROM product_reviews 
    WHERE product_id = p.product_id
)
WHERE pr.product_id IS NOT NULL;

-- Multiple table update
UPDATE 
    orders o,
    order_items oi,
    products p
SET 
    o.status = 'out_of_stock',
    oi.status = 'cancelled',
    p.stock_quantity = 0
WHERE 
    o.order_id = oi.order_id
    AND oi.product_id = p.product_id
    AND p.discontinued = TRUE;

-- ========================================
-- UPDATE WITH LIMIT AND ORDER BY
-- ========================================

-- Update only first 10 rows
UPDATE products 
SET discount_percent = 20
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 10;

-- Update oldest records
UPDATE user_logs 
SET archived = TRUE
WHERE created_at < '2024-01-01'
ORDER BY created_at ASC
LIMIT 1000;

-- ========================================
-- SAFE UPDATE PRACTICES
-- ========================================

-- Always use transactions for important updates
START TRANSACTION;

UPDATE accounts 
SET balance = balance - 100
WHERE account_id = 1001;

UPDATE accounts 
SET balance = balance + 100
WHERE account_id = 1002;

-- Check the changes
SELECT * FROM accounts WHERE account_id IN (1001, 1002);

-- If correct:
COMMIT;
-- If wrong:
-- ROLLBACK;

-- Test with SELECT first
SELECT * FROM users 
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);
-- Then UPDATE
UPDATE users 
SET status = 'dormant'
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- ========================================
-- UPDATE WITH VALUES FROM ANOTHER TABLE
-- ========================================

-- Update from temporary table
CREATE TEMPORARY TABLE price_updates (
    product_id INT,
    new_price DECIMAL(10,2)
);

INSERT INTO price_updates VALUES 
    (1, 29.99),
    (2, 39.99),
    (3, 49.99);

UPDATE products p
INNER JOIN price_updates pu ON p.product_id = pu.product_id
SET p.price = pu.new_price;

-- Update with correlated subquery
UPDATE orders o
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.order_id
)
WHERE status = 'pending';

-- ========================================
-- BULK UPDATE OPTIMIZATION
-- ========================================

-- Use CASE for different updates
UPDATE users 
SET role = CASE user_id
    WHEN 1 THEN 'admin'
    WHEN 2 THEN 'editor'
    WHEN 3 THEN 'author'
    ELSE role
END
WHERE user_id IN (1, 2, 3);

-- Batch updates with prepared statements
PREPARE update_price FROM 
'UPDATE products SET price = ? WHERE product_id = ?';

SET @price1 = 99.99; SET @id1 = 1;
EXECUTE update_price USING @price1, @id1;

SET @price2 = 149.99; SET @id2 = 2;
EXECUTE update_price USING @price2, @id2;

DEALLOCATE PREPARE update_price;