Gotcha: PostgreSQL UPDATE with JOIN syntax differs from MySQL

MySQL-style UPDATE ... JOIN syntax fails in PostgreSQL. The syntax for updating with data from another table is completely different.

PostgreSQL uses FROM clause instead of JOIN:

-- MySQL syntax (fails in PostgreSQL):
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'vip'
WHERE c.tier = 'gold';

-- PostgreSQL syntax:
UPDATE orders
SET status = 'vip'
FROM customers
WHERE orders.customer_id = customers.id
AND customers.tier = 'gold';

-- PostgreSQL with multiple tables:
UPDATE orders
SET total = orders.subtotal + shipping.cost
FROM shipping
WHERE orders.id = shipping.order_id;

-- PostgreSQL UPDATE ... RETURNING (get affected rows):
UPDATE orders
SET status = 'shipped'
FROM shipping
WHERE orders.id = shipping.order_id
RETURNING orders.id, orders.status;