Update Existing Rows in Tables

Updating data is where a database earns its keep — but it’s also where careless SQL can silently corrupt hours of work. Whether you’re fixing a typo in a user profile, adjusting a price, or migrating a status field across millions of rows, the UPDATE statement is your scalpel. In this lesson, you’ll learn to wield it precisely: how to construct safe updates, avoid common foot-guns, and decide between row-level edits and bulk strategies. By the end, you’ll not just know the syntax — you’ll trust your updates.

The problem this lesson solves

You’ve inserted data into your tables — users, orders, products. But the world isn’t static. Emails change, stock levels fluctuate, and your boss just asked you to apply a 10% discount to all items in the "clearance" category. Without a solid grasp of updating existing rows, you’re stuck re-inserting records (bad idea) or running risky blanket updates that could nuke your data.

The UPDATE statement is how you modify one or more existing rows in a PostgreSQL table. It’s a core part of the CRUD toolkit (Create, Read, Update, Delete), and it’s something you’ll use daily in real applications. The pain: a single mistyped WHERE clause can update thousands of rows you never intended to touch. This lesson shows you how to update with confidence — using precise conditions, safe patterns, and sanity checks.

Core concept / mental model

Think of a table as a spreadsheet, and UPDATE as editing specific cells in specific rows. You always specify which rows to change (the WHERE clause) and what to change (the SET clause). If you forget the WHERE, you’ve just edited every row in the table — like changing every cell in a column without meaning to.

Here’s the mental model in SQL terms:

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

• The SET clause defines new values for one or more columns.
• The WHERE clause filters which rows get updated. If omitted, all rows are updated.
• PostgreSQL evaluates the WHERE condition per row, and only rows that match are changed.

It’s also helpful to think about atomicity: an UPDATE runs as a single transaction by default. Either it succeeds entirely, or it rolls back if something fails. You won’t end up with half-updated rows — that’s a key safety net.

How it works step by step

Let’s break down the update process logically:

1. Identify the table you want to modify.
2. Decide which columns need new values (the SET clause).
3. Write the WHERE condition to target exactly the rows you want. This is the most critical step — test it with a SELECT first if you’re unsure.
4. Execute the update. PostgreSQL locks the affected rows for the duration — others will see the old version until you commit (if inside a transaction).
5. Verify the results by running a SELECT to confirm the changes.

The order matters: you’re not inserting new rows, but altering existing ones. Unlike INSERT (which adds), UPDATE preserves the row identity — primary keys stay the same, and any indexes are updated automatically.

Hands-on walkthrough

Let’s get our hands dirty. We’ll start with a sample products table to see how updates work in practice.

-- Create a sample table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price NUMERIC(10,2),
    in_stock BOOLEAN DEFAULT true
);

-- Insert some data
INSERT INTO products (name, category, price, in_stock) VALUES
('Widget', 'Gadgets', 19.99, true),
('Gadget Pro', 'Gadgets', 49.99, true),
('Thingamajig', 'Gadgets', 29.99, false),
('Doodad', 'Gizmos', 9.99, true);

Now, let’s update a single row — change the price of the 'Widget' to 24.99:

UPDATE products
SET price = 24.99
WHERE name = 'Widget';

To check what changed:

SELECT * FROM products WHERE name = 'Widget';

Expected output:

 id |  name  | category | price | in_stock 
----+--------+----------+-------+----------
  1 | Widget | Gadgets  | 24.99 | t

Now update multiple columns at once — set the 'Thingamajig' to be in stock and apply a discount:

UPDATE products
SET in_stock = true, price = price * 0.9
WHERE id = 3;

Notice you can reference the existing column value in the expression — price * 0.9 uses the current price.

Expected output:

UPDATE 1

That UPDATE 1 means one row was affected — a quick sanity check.

Compare options / when to choose what

There’s more than one way to update data. Here’s a comparison of common approaches:

For most day-to-day updates, a simple UPDATE with WHERE suffices. Use a subquery when the new value depends on data from another table. For bulk updates joining multiple tables, UPDATE ... FROM is your friend. If you need to insert or update based on existence, consider upsert.

Troubleshooting & edge cases

Forgetting the WHERE clause

The classic disaster: running UPDATE products SET in_stock = true; updates every row. Always test your WHERE with a SELECT first:

-- Test the condition
SELECT * FROM products WHERE category = 'Gadgets';

-- Then update
UPDATE products SET in_stock = true WHERE category = 'Gadgets';

Condition matches nothing

If your WHERE matches zero rows, PostgreSQL simply returns UPDATE 0 — no error. This can be confusing; it’s often a typo in the condition. Double-check your values and the actual data in the table.

Data type mismatches

Updating a numeric column with a string will raise an error like invalid input syntax for type numeric. Ensure the new value’s type matches the column’s type, or cast explicitly: SET price = '24.99'::numeric.

Concurrency issues

If two transactions update the same row simultaneously, PostgreSQL uses row-level locking. The second update will wait until the first commits. If you don’t want to wait, you can use NOWAIT or handle lock timeouts in your application.

What you learned & what's next

You’ve mastered the core of updating existing rows in tables: you can now craft precise UPDATE statements, update multiple columns in one go, and avoid the infamous WHERE-less wipe-out. You also understand when to use simple updates versus subqueries or upserts.

This is a key milestone in your PostgreSQL journey — you’re no longer just inserting static data; you’re dynamically maintaining it. Next up, you’ll learn how to delete rows safely with DELETE, and later, how to combine INSERT, UPDATE, and DELETE in transactions for robust data operations.

Keep practicing, and always remember: with great update power comes great responsibility.