What happens if I run UPDATE or DELETE without a WHERE clause?

The statement applies to every row in the table. UPDATE without WHERE rewrites all rows, and DELETE without WHERE empties the table. Always run the same WHERE as a SELECT first to preview which rows will be affected.

How do I undo an UPDATE or DELETE in SQL?

Wrap the statement in a transaction with BEGIN. Nothing is permanent until you COMMIT, so if the result is wrong you can run ROLLBACK to reverse every change since BEGIN. Once you COMMIT, the change is permanent.

Can I UPDATE a table using values from another table?

Yes. In PostgreSQL use UPDATE target SET col = other.col FROM other WHERE target.key = other.key. The table being changed is named after UPDATE, and the join condition goes in the WHERE clause.

Beginner

SQL UPDATE and DELETE: Safe Data Modification

Reading data is forgiving; changing it is not. Learn to UPDATE and DELETE rows precisely — with WHERE, joins, RETURNING, and transactions that let you undo a mistake.

SELECT can be wrong and you simply re-run it. UPDATE and DELETE are different — they change the data, and a careless statement can rewrite or erase thousands of rows in an instant. The good news is that a few habits make data modification safe and predictable. This guide covers them. Keep the SQL cheat sheet nearby for syntax.

UPDATE Basics

UPDATE changes values in existing rows. You name the table, set one or more columns, and — crucially — say which rows with WHERE.

-- Mark one order as paid
UPDATE orders
SET status = 'paid',
    paid_at = NOW()
WHERE id = 4271;

The WHERE Clause

The WHERE clause decides which rows change. Leave it off and UPDATE touches every row in the table — the single most expensive beginner mistake in SQL.

-- DANGER: no WHERE updates EVERY row
UPDATE orders SET status = 'paid';

-- Correct: scope it
UPDATE orders
SET status = 'paid'
WHERE id = 4271;

UPDATE with a JOIN

Sometimes the new value comes from another table. PostgreSQL uses UPDATE ... FROM to join during an update — for example, applying a discount stored on a related table.

-- Copy each user's tier discount onto their open orders
UPDATE orders o
SET discount = u.tier_discount
FROM users u
WHERE o.user_id = u.id
  AND o.status = 'pending';

DELETE Basics

DELETE removes whole rows. Like UPDATE, it lives and dies by its WHERE clause.

-- Remove a single cancelled order
DELETE FROM orders
WHERE id = 4271;

-- Remove all abandoned carts older than 90 days
DELETE FROM carts
WHERE status = 'abandoned'
  AND updated_at < NOW() - INTERVAL '90 days';

RETURNING — See What Changed

PostgreSQL can hand back the rows it just modified with RETURNING. This is invaluable for confirming the impact of a statement and for capturing generated values like a new id.

-- Update and immediately see the affected rows
UPDATE orders
SET status = 'refunded'
WHERE id = 4271
RETURNING id, status, total;

-- Works for DELETE too
DELETE FROM carts
WHERE status = 'abandoned'
RETURNING id;

Transactions — Your Undo Button

A transaction groups statements so they all succeed or all roll back. Wrapping a risky change in a transaction gives you a chance to inspect the result and undo it if something looks wrong.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

-- Inspect the result; if it's wrong:
ROLLBACK;   -- undo everything since BEGIN

-- if it's right:
-- COMMIT;  -- make it permanent

Safety Tips

Always SELECT with your WHERE clause first to preview the affected rows
Never run UPDATE or DELETE without a WHERE unless you truly mean "all rows"
Wrap risky changes in BEGIN ... COMMIT so you can ROLLBACK
Use RETURNING to confirm exactly what changed
Be aware of foreign keys — deleting a parent row may cascade or fail depending on the constraint

The safest place to practice destructive statements is one you can reset. Try them in the SQL Sandbox — a private database you can break and rebuild as many times as you like.