Updating Data

-- Update a single customer's city
UPDATE customers
SET city = 'Brooklyn'
WHERE id = 1;

-- Update multiple columns at once — separate with commas
UPDATE employees
SET job_title = 'Senior Account Manager',
    salary    = 65000.00
WHERE id = 5;

-- Verify the change
SELECT id, job_title, salary FROM employees WHERE id = 5;

-- DANGEROUS — updates EVERY order's status to 'shipped'
UPDATE orders SET status = 'shipped';

-- CORRECT — only updates the specific order
UPDATE orders SET status = 'shipped' WHERE id = 42;

-- Safe practice: run a SELECT with the same WHERE first to preview affected rows
SELECT id, status FROM orders WHERE id = 42;

-- Once confirmed, run the UPDATE
UPDATE orders SET status = 'shipped' WHERE id = 42;

-- Give all Electronics products a 10% price reduction
UPDATE products
SET price = price * 0.90
WHERE category = 'Electronics';

-- Increase all Sales department salaries by $5,000
UPDATE employees
SET salary = salary + 5000
WHERE department = 'Sales';

-- Add 20 units to stock for a specific product
UPDATE products
SET stock_qty = stock_qty + 20
WHERE id = 3;

-- Deactivate all products with zero stock
UPDATE products
SET is_active = FALSE
WHERE stock_qty = 0;

SELECT name, price FROM products WHERE category = 'Electronics' LIMIT 3;

-- Update and immediately see the new values
UPDATE products
SET price = price * 1.05,   -- 5% price increase
    stock_qty = stock_qty - 1
WHERE id = 7
RETURNING id, name, price, stock_qty;

-- Update an order status and return the updated row
UPDATE orders
SET status = 'delivered'
WHERE id = 15
RETURNING id, customer_id, status, total_amount;

-- Update order total based on the sum of its line items
UPDATE orders
SET total_amount = (
  SELECT SUM(quantity * unit_price)
  FROM order_items
  WHERE order_items.order_id = orders.id
)
WHERE status = 'pending';

-- Deactivate products that have never appeared in any order
UPDATE products
SET is_active = FALSE
WHERE id NOT IN (
  SELECT DISTINCT product_id FROM order_items
);

SELECT COUNT(*) FROM orders WHERE total_amount IS NOT NULL;

-- Update order status to 'vip' for customers in the vip_customers table
UPDATE orders
SET status = 'vip-pending'
FROM vip_customers
WHERE orders.customer_id = vip_customers.id
  AND orders.status = 'pending';

-- Give a 15% raise to employees in the same department as employee id 5
UPDATE employees AS e1
SET salary = e1.salary * 1.15
FROM employees AS e2
WHERE e1.department = e2.department
  AND e2.id = 5
  AND e1.id != 5;   -- don't update the reference employee again

SELECT first_name, salary FROM employees WHERE department = 'Sales' LIMIT 3;

-- Assign customer tier based on their total spending
UPDATE customers
SET tier = CASE
  WHEN id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(total_amount) > 1000) THEN 'gold'
  WHEN id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(total_amount) > 500)  THEN 'silver'
  ELSE 'standard'
END;

-- Apply different discounts based on product category
UPDATE products
SET price = CASE
  WHEN category = 'Electronics' THEN price * 0.85   -- 15% off electronics
  WHEN category = 'Furniture'   THEN price * 0.90   -- 10% off furniture
  ELSE price * 0.95                                  -- 5% off everything else
END;

SELECT name, category, price FROM products LIMIT 5;