In PostgreSQL, the UPDATE statement is used to modify existing records in a table. Here are different ways you can use UPDATE:

1. Basic UPDATE Statement

🔹 Update a single column:

UPDATE usersSET email = 'newemail@example.com'WHERE id = 1;

🔹 Update multiple columns:

UPDATE usersSET email = 'newemail@example.com', name = 'John Doe'WHERE id = 1;

2. Update with a Condition

🔹 Increase the salary of employees in the IT department by 10%:

UPDATE employeesSET salary = salary * 1.1WHERE department = 'IT';

3. Using RETURNING to Get Updated Data

🔹 Get the updated row after modifying it:

UPDATE usersSET email = 'updated@example.com'WHERE id = 1RETURNING *;

(Useful for verifying the changes in applications)

4. Update Using Data from Another Table (JOIN)

🔹 Example: Update users' country names based on a countries table:

UPDATE usersSET country = countries.nameFROM countriesWHERE users.country_id = countries.id;

5. Conditional Update with CASE

🔹 Set different discounts based on product category:

UPDATE productsSET discount =     CASE         WHEN category = 'Electronics' THEN 10        WHEN category = 'Clothing' THEN 20        ELSE 5    END;

6. Bulk Update with IN Clause

🔹 Update multiple records using IN:

UPDATE ordersSET status = 'Shipped'WHERE id IN (101, 102, 103);

7. Update Using a Subquery

🔹 Set user rank based on total order amount:

UPDATE usersSET rank = (SELECT 'VIP' WHERE (SELECT SUM(total_amount) FROM orders WHERE orders.user_id = users.id) > 1000)WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

8. Update a JSONB Field

🔹 Modify a key inside a JSONB column:

UPDATE usersSET profile = jsonb_set(profile, '{city}', '"New York"')WHERE id = 1;

9. Update with Auto-Increment Restart

🔹 Reset an auto-incremented column:

ALTER SEQUENCE users_id_seq RESTART WITH 1;

10. Using Transactions for Safe Updates

If you need to update multiple tables together, wrap them in a transaction:

BEGIN;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT;

(Ensures both updates succeed, preventing partial updates)

Summary

✅ UPDATE can modify single or multiple columns
✅ Use RETURNING to get updated rows
✅ JOIN, IN, CASE, and JSONB allow complex updates
✅ Use transactions (BEGIN ... COMMIT) for multi-step updates