PostgreSQL - UPDATE

PostgreSQL - The UPDATE Statement

The UPDATE statement is used to modify existing records in a table. It is almost always used with a WHERE clause to specify which rows to update. Without a WHERE clause, the UPDATE statement will modify all rows in the table, which can be a destructive and unintended action.

Syntax

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

• table_name: The table you want to update.
• SET column = value: The column(s) to modify and their new value(s).
• WHERE condition: The condition that identifies the specific row(s) to update. This is a critical part of the statement.

Example: Updating a Single Record

Let’s work with a products table. First, let’s create it and add some data.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    stock_quantity INT
);

INSERT INTO products (name, price, stock_quantity) VALUES
('Laptop', 1200.00, 50),
('Keyboard', 75.50, 120),
('Mouse', 25.00, 200),
('Monitor', 300.00, 80);

Now, let’s say we need to correct the price of the ‘Mouse’. We can update it using its id or name.

UPDATE products
SET price = 26.50
WHERE name = 'Mouse';

After this query, the ‘Mouse’ row will have its price updated. You can verify this with SELECT * FROM products WHERE name = 'Mouse';.

Updating Multiple Records

You can update multiple rows that match a condition. For instance, let’s apply a 10% discount to all products with a price over $100.

UPDATE products
SET price = price * 0.90
WHERE price > 100.00;

This will update the ‘Laptop’ and ‘Monitor’ rows.

Modern Feature: The RETURNING Clause

A powerful PostgreSQL feature is the RETURNING clause, which allows you to get back the values of the rows that were just updated. This is extremely useful in application development as it avoids a subsequent SELECT query.

Let’s update the stock for the ‘Keyboard’ and see the updated row immediately.

UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE name = 'Keyboard'
RETURNING id, name, stock_quantity;

This query will not only update the database but also return the following result:

id	name	stock_quantity
2	Keyboard	115

Safety and Best Practices

• Always use a WHERE clause: Forgetting the WHERE clause is a common and dangerous mistake. It will update every single row in the table.
• Use Transactions: When performing critical updates, wrap your UPDATE statement in a transaction (BEGIN; ... COMMIT;). This allows you to review the changes before making them permanent or to ROLLBACK if something goes wrong.
• Test with SELECT: Before running an UPDATE, you can write a SELECT statement with the same WHERE clause to preview which rows will be affected. For example: SELECT * FROM products WHERE price > 100.00;.
• Use Primary Keys: When updating a single, specific row, it’s safest to use the primary key in the WHERE clause (e.g., WHERE id = 3) as it guarantees you are targeting exactly one row.