Part 45: Updating with Joins: Cross-Table Updates

Updating rows in Table A based on values matching in Table B.

1. PostgreSQL Syntax (UPDATE ... FROM)

UPDATE products p
SET price = p.price * 0.9
FROM categories c
WHERE p.category_id = c.id
  AND c.name = 'Clearance';

2. MySQL Syntax (UPDATE table1 JOIN table2)

UPDATE products p
JOIN categories c ON p.category_id = c.id
SET p.price = p.price * 0.9
WHERE c.name = 'Clearance';

Key Takeaways

• Use UPDATE ... FROM in PostgreSQL and UPDATE ... JOIN in MySQL to update based on joined relationships.

In Part 46, we explore Deleting Rows with DELETE FROM & Filtered Deletions!