PostgreSQL UPDATE Statement

The UPDATE statement in PostgreSQL is used to modify existing records in a table. It allows you to change one or more column values based on specific conditions. Without a WHERE clause, all rows will be updated, so use it carefully.

1. Basic UPDATE Syntax

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

- SET: defines the new values for one or more columns - WHERE: specifies which rows should be updated - If WHERE is omitted, all rows will be updated

2. Example: Updating a Single Row

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary NUMERIC(10,2)
);

-- Update salary of one employee
UPDATE employees
SET salary = 60000
WHERE id = 1;

3. Updating Multiple Columns

UPDATE employees
SET department = 'Marketing',
    salary = 65000
WHERE name = 'Alice Johnson';

4. Updating Multiple Rows

You can update multiple rows at once if they match the condition.

-- Give a 10% salary increase to IT department
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'IT';

5. Updating All Rows

If you don’t use a WHERE clause, all rows will be updated.

-- Set all employees' department to 'General'
UPDATE employees
SET department = 'General';

6. UPDATE with Subquery

You can use a subquery to update values based on data from another table.

CREATE TABLE bonuses (
  emp_id INT,
  bonus NUMERIC(10,2)
);

-- Update salary based on bonus table
UPDATE employees
SET salary = salary + b.bonus
FROM bonuses b
WHERE employees.id = b.emp_id;

7. UPDATE with RETURNING Clause

Just like INSERT, the UPDATE statement supports the RETURNING clause to return updated rows immediately.

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Finance'
RETURNING id, name, salary;

This will return the id, name, and new salaryof all updated employees.

8. Conditional Updates (CASE Expression)

You can use the CASE expression to perform conditional updates.

-- Increase salary based on department
UPDATE employees
SET salary = salary + 
  CASE 
    WHEN department = 'HR' THEN 3000
    WHEN department = 'IT' THEN 5000
    ELSE 2000
  END;

Best Practices

• Always use a WHERE clause to avoid accidental mass updates.
• Use RETURNING to confirm which rows were updated.
• Consider using transactions (BEGIN ... COMMIT) for critical updates.
• Index frequently updated columns for better performance.

Conclusion

The UPDATE statement is a powerful tool in PostgreSQL for modifying data. By combining it with conditions, subqueries, and the RETURNING clause, you can efficiently manage and verify updates. In the next tutorial, we’ll explore how to delete data safely from PostgreSQL tables using the DELETE statement.