UPDATE - Changing Data

The UPDATE statement modifies existing rows in a table.

Basic Syntax

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

Critical: Always include a WHERE clause! Without it, you'll update every row in the table.

Updating a Single Column

-- Set a specific product's price
UPDATE products
SET price = 89.99
WHERE name = 'Keyboard';

Updating Multiple Columns

UPDATE products
SET price = 54.99, in_stock = false, quantity = 0
WHERE name = 'Webcam';

Using Expressions

You can use the current value in calculations:

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

-- Increase all quantities by 10
UPDATE products
SET quantity = quantity + 10;

UPDATE with RETURNING

Like INSERT, UPDATE supports RETURNING:

UPDATE products
SET price = price * 1.15
WHERE category = 'Furniture'
RETURNING name, price;

Conditional Updates with CASE

UPDATE products
SET price = CASE
  WHEN category = 'Electronics' THEN price * 0.95
  WHEN category = 'Furniture' THEN price * 0.90
  ELSE price
END;

Safety Tips

1. Always test your WHERE clause first with a SELECT:

      -- First, check what will be affected:
      SELECT * FROM products WHERE category = 'Electronics';
   
      -- Then update:
      UPDATE products SET price = price * 0.9 WHERE category = 'Electronics';
      
   

2. Use RETURNING to verify what was changed

3. Wrap in a transaction for important updates (we'll learn about transactions later)

Try It Yourself

1. Update the price of "Mouse" to 24.99
2. Mark all products with quantity 0 as out of stock (in_stock = false)
3. Increase all prices by 5% and see the updated values with RETURNING