PostgreSQL UPDATE and DELETE

After data is inserted into a table, it often needs to be modified or removed. The UPDATE statement changes existing records, and the DELETE statement removes them. Both commands accept a WHERE clause to target specific rows. Without a WHERE clause, both commands affect every row in the table — a critical detail to understand before using them.

The UPDATE Statement

Basic Syntax

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

The SET clause specifies which columns to change and what new values to assign. The WHERE clause identifies which rows to update. If WHERE is omitted, every row in the table gets updated.

Updating a Single Column

UPDATE students
SET age = 23
WHERE name = 'Alice';

Only the row where name = 'Alice' has its age changed to 23. All other rows remain untouched.

Updating Multiple Columns at Once

UPDATE employees
SET salary = 75000, department = 'Senior IT'
WHERE id = 3;

Both the salary and department are updated for the employee with id 3 in a single statement.

Updating Based on a Calculation

The new value can be derived from the column's current value:

UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';

This increases the price of all electronics by 10%. The current value of price is multiplied by 1.10 and written back to the column.

Updating All Rows (No WHERE)

UPDATE employees
SET bonus = 500;

Every employee in the table receives a bonus of 500. This is intentional here, but accidentally omitting WHERE when targeting specific rows is one of the most common and costly mistakes in SQL.

UPDATE with RETURNING

Just like INSERT, UPDATE supports the RETURNING clause to see what was changed:

UPDATE employees
SET salary = 80000
WHERE id = 2
RETURNING id, name, salary;

The result shows the updated row immediately, confirming what the new values look like.

The DELETE Statement

Basic Syntax

DELETE FROM table_name
WHERE condition;

DELETE removes rows from a table. The WHERE clause identifies which rows to delete. Without WHERE, all rows are deleted.

Deleting a Specific Row

DELETE FROM students
WHERE id = 5;

Only the student with id 5 is removed. The table structure and all other rows remain intact.

Deleting Multiple Rows

DELETE FROM orders
WHERE status = 'cancelled';

All rows where the status is 'cancelled' are deleted at once.

Deleting All Rows

DELETE FROM logs;

This removes every row from the logs table but keeps the table itself. The table structure (columns, constraints) is preserved. To also remove the table, DROP TABLE would be used instead.

DELETE with RETURNING

DELETE FROM students
WHERE age > 30
RETURNING id, name;

The deleted rows are returned before they are removed, which is useful for logging or auditing purposes.

TRUNCATE — A Faster Way to Delete All Rows

TRUNCATE removes all rows from a table much faster than a full DELETE with no WHERE clause, because it bypasses row-by-row processing:

TRUNCATE TABLE logs;

The key differences between DELETE and TRUNCATE:

Safety Practices for UPDATE and DELETE

Always Test with SELECT First

Before running an UPDATE or DELETE, run the equivalent SELECT with the same WHERE clause to confirm which rows will be affected:

-- Check before deleting
SELECT * FROM orders WHERE status = 'cancelled';

-- If the result looks correct, proceed
DELETE FROM orders WHERE status = 'cancelled';

Use Transactions for Safety

Wrap UPDATE and DELETE operations in a transaction so they can be rolled back if something goes wrong. Transactions are covered in detail in a later topic.

BEGIN;

DELETE FROM students WHERE id = 10;

-- Check the result before finalizing
SELECT * FROM students;

-- If everything looks correct
COMMIT;

-- If something went wrong
-- ROLLBACK;

Example: UPDATE and DELETE in Practice

CREATE TABLE inventory (
    id SERIAL PRIMARY KEY,
    item_name VARCHAR(100),
    quantity INT,
    status VARCHAR(20)
);

INSERT INTO inventory (item_name, quantity, status)
VALUES
    ('Pen', 500, 'active'),
    ('Eraser', 200, 'active'),
    ('Ruler', 0, 'out_of_stock'),
    ('Stapler', 0, 'out_of_stock'),
    ('Highlighter', 150, 'active');

-- Restock rulers
UPDATE inventory
SET quantity = 100, status = 'active'
WHERE item_name = 'Ruler';

-- Give a 5% quantity bonus to all active items
UPDATE inventory
SET quantity = quantity + (quantity * 0.05)
WHERE status = 'active';

-- Remove all permanently out-of-stock items
DELETE FROM inventory
WHERE status = 'out_of_stock'
RETURNING item_name;

Key Points

• UPDATE modifies existing rows; DELETE removes them.
• Always include a WHERE clause unless the intent is to affect every row in the table.
• Test with SELECT using the same WHERE condition before running UPDATE or DELETE.
• RETURNING displays the affected rows after UPDATE or DELETE.
• TRUNCATE removes all rows faster than DELETE but cannot filter rows with WHERE.
• Using transactions (BEGIN / COMMIT / ROLLBACK) provides a safety net for destructive operations.