Right, let’s talk about UPDATE. It’s the SQL command that lets you fix your data, or, if you’re not careful, spectacularly ruin it. The core concept is simple: find some rows and change some values. The execution is where you earn your paycheck. The basic syntax is a three-act play:

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

Forget the WHERE clause, and you’ve just performed a table-wide “oops.” It’s the database equivalent of using rm -rf / because you wanted to clean up a single file in your downloads folder. The SET clause is where the magic (or the tragedy) happens, allowing you to use expressions, not just literal values.

The Almighty WHERE Clause: Your Safety Harness

I cannot overstate this: your WHERE clause is your only lifeline. The database trusts you implicitly. It assumes you know exactly which rows you intend to update. Before you hit enter, run a SELECT with the same WHERE clause.

Want to give everyone in the ‘Shipping’ department a raise?

-- First, do this. Always.
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Shipping';

-- Looks good? Okay, *now* do the update.
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Shipping';

This simple habit is the difference between a promotion and a frantic call to restore last night’s backup at 2 a.m.

The SET Clause: More Than Just Assignment

The SET clause is fantastically powerful. You’re not limited to setting a column to a static value; you can use expressions that reference other columns, call functions, or use case statements.

Need to reset a password and also timestamp when it was done? Don’t make two queries.

UPDATE users
SET
    password_hash = crypt('new_secret_password', gen_salt('bf')),
    password_changed_at = NOW()
WHERE user_id = 42;

Or perhaps you need to toggle a boolean field:

UPDATE tasks
SET is_complete = NOT is_complete
WHERE task_id = 101;

You can even reference the current value of another column in the same row. This isn’t a join; it’s a direct, row-local operation.

UPDATE products
SET
    price = price * 0.8, -- 20% discount
    is_on_sale = TRUE    -- and mark it as on sale
WHERE discontinued = FALSE;

Expressions and Functions: Your Toolbox

This is where you make the database work for you. You can use almost any function in your expression.

Fixing data formatting? Use a function.

-- Let's standardize those messy email addresses to lowercase
UPDATE customers
SET email = LOWER(email)
WHERE email != LOWER(email); -- Cleverly only update what needs it

Need a conditional update? Use a CASE statement right in the SET clause. This is far more efficient than multiple round trips from your application.

-- Apply a different discount based on customer tier
UPDATE orders
SET discount_amount = CASE
    WHEN customer_tier = 'Gold' THEN total_amount * 0.15
    WHEN customer_tier = 'Silver' THEN total_amount * 0.10
    ELSE total_amount * 0.05
END
WHERE order_status = 'Pending';

The RETURNING Clause: Your Instant Confirmation

This is PostgreSQL’s killer feature that I wish every database had. It saves you a follow-up SELECT query by returning the affected rows right from the update statement. It’s the command saying, “Here, see what I just did.”

-- Update the user and get their new info in one shot
UPDATE users
SET last_login = NOW()
WHERE username = 'alice'
RETURNING user_id, username, last_login;

This is invaluable for debugging (“did I really just update that?”) and for application logic where you immediately need the updated data. Use it relentlessly. It makes your code cleaner and your life easier.