UPDATE Statement in PostgreSQL

UPDATE modifies existing rows in a table.

Basic Syntax

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

Example

UPDATE employees
SET salary = 70000
WHERE id = 1;

Always include a WHERE clause — omitting it updates every row in the table:

UPDATE employees SET department = 'Engineering';
-- every single employee is now in Engineering — almost certainly not intended

Updating Multiple Columns

UPDATE employees
SET department = 'Management', salary = salary * 1.1
WHERE id = 3;

Note that salary = salary * 1.1 reads the current value as part of the same statement — a 10% raise based on the existing salary.

Updating Using Data from Another Table

UPDATE employees e
SET department = d.name
FROM departments d
WHERE e.department_id = d.id;

RETURNING the Updated Rows

UPDATE employees
SET salary = salary + 5000
WHERE department = 'Engineering'
RETURNING id, name, salary;

Common Errors & Troubleshooting

• Forgetting WHERE and updating every row — always run the equivalent SELECT ... WHERE ... first to confirm exactly which rows will be affected before running the real UPDATE.
• UPDATE ... FROM matching more rows than expected — a missing or incorrect join condition in the FROM/WHERE clause can cause a row to be updated multiple times (once per matching join row) — double check the join condition is as selective as you intend.
• ERROR: column "department" of relation "employees" does not exist — usually a typo, or the column was renamed by an earlier ALTER TABLE you forgot about — check \d employees to confirm the current column names.

Key Takeaways

• A missing WHERE clause means "update every row" — always double-check it before running.
• You can reference a column's current value on the right-hand side of its own assignment.
• UPDATE ... FROM lets you update one table based on values from another.