PostgreSQL UPDATE
Learn how to modify existing data in PostgreSQL tables
✏️ What is UPDATE?
UPDATE is a SQL command that modifies existing data in table rows. It allows you to change one or more column values based on specified conditions, keeping your database information current.
-- Update a user's email
UPDATE users
SET email = '[email protected]'
WHERE id = 1;
Output:
✓ UPDATE 1 (1 row updated)
Key UPDATE Concepts
Single Column
Update one column value
UPDATE products
SET price = 99.99
WHERE product_id = 5;Multiple Columns
Update several columns at once
UPDATE users
SET name = 'John',
email = '[email protected]'
WHERE id = 1;WHERE Clause
Specify which rows to update
UPDATE products
SET stock = 0
WHERE stock < 5;RETURNING
Get updated values back
UPDATE users
SET status = 'active'
WHERE id = 1
RETURNING *;🔹 Update Single Column
The basic UPDATE statement modifies one column for rows matching the WHERE condition. Always use WHERE to avoid updating all rows accidentally. Without WHERE, every row in the table will be updated.
-- Update product price
UPDATE products
SET price = 149.99
WHERE product_id = 10;
Output:
✓ UPDATE 1
🔹 Update Multiple Columns
You can update multiple columns in a single statement by separating them with commas. This is more efficient than multiple UPDATE statements. All changes happen atomically, ensuring data consistency across columns.
-- Update multiple employee details
UPDATE employees
SET salary = 85000,
department = 'Engineering',
is_active = true
WHERE emp_id = 5;
Output:
✓ UPDATE 1 (3 columns updated)
🔹 Update with Conditions
Use WHERE clause with complex conditions to target specific rows. You can combine multiple conditions using AND, OR operators. This gives you precise control over which records get updated in your database.
-- Update products in specific category with low stock
UPDATE products
SET status = 'reorder_needed'
WHERE category = 'Electronics'
AND stock_quantity < 10;
-- Update based on date
UPDATE orders
SET status = 'archived'
WHERE order_date < '2024-01-01'
AND status = 'completed';
Output:
✓ UPDATE 15 (15 rows updated)
🔹 Update with Calculations
You can use expressions and calculations in UPDATE statements. This allows you to modify values based on their current state. Common uses include incrementing counters, applying discounts, or adjusting quantities.
-- Increase all prices by 10%
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';
-- Decrease stock quantity
UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 20;
-- Concatenate strings
UPDATE users
SET full_name = first_name || ' ' || last_name;
Output:
✓ UPDATE 8 (prices increased)
🔹 Update with RETURNING
The RETURNING clause returns the updated rows, allowing you to see the changes immediately. You can return specific columns or all columns using asterisk. This eliminates the need for a separate SELECT query after updating.
-- Update and return the updated row
UPDATE employees
SET salary = salary * 1.05
WHERE emp_id = 3
RETURNING emp_id, first_name, salary;
-- Return all columns
UPDATE products
SET stock_quantity = stock_quantity + 100
WHERE product_id = 15
RETURNING *;
Output:
| emp_id | first_name | salary |
|---|---|---|
| 3 | Alice | 78750.00 |
🔹 Update from Another Table
You can update a table based on values from another table using FROM clause. This is useful for synchronizing data between related tables. It enables complex updates based on relationships and joined data.
-- Update product prices based on category discounts
UPDATE products
SET price = price * (1 - c.discount_rate)
FROM categories c
WHERE products.category_id = c.category_id
AND c.discount_rate > 0;
Output:
✓ UPDATE 12 (prices updated from categories)