SQL UPDATE Statement: Safe Updates, UPDATE With JOIN and RETURNING
The SQL UPDATE statement changes existing rows. Learn the syntax, updating several columns, CASE-based updates, updating from another table with a join, the correlated subquery trap that silently sets values to NULL, guarded updates, RETURNING, and how to never lose data to a missing WHERE, all tested.
- The SQL UPDATE statement changes rows that match
WHERE. WithoutWHERE, it changes every row; our test hit all 5. - Check the affected row count. A guarded
WHERE stock > 0update returned 0 rows for an out-of-stock item, which is how you detect failure. - Updating from a correlated subquery without a matching
WHERE EXISTSset 3 of 5 prices to NULL. - Run risky updates inside a transaction, preview with
SELECTfirst, and useRETURNINGto see exactly what changed.
Inserting data is easy to undo: delete the new row. Updating data is not, because the old value is gone the moment the statement commits. That is why the SQL UPDATE statement deserves more care than its simple syntax suggests. A missing WHERE, a join that matches nothing, or a subquery that returns NULL can quietly damage thousands of rows.
We ran every statement on SQLite 3.50.4 through Python and show the real output, including the row counts. Join syntax for MySQL and SQL Server is included from their documentation and was not run here.
SQL UPDATE syntax
UPDATE table_name
SET column1 = value1,
column2 = value2
WHERE condition;
SET lists the columns to change and their new values, which can be constants, expressions or subqueries. WHERE decides which rows change. The database reports how many rows were affected, available as cursor.rowcount in Python, ROW_COUNT() in MySQL and @@ROWCOUNT in SQL Server.
The sample data
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT,
price REAL, stock INT, updated_at TEXT);
INSERT INTO products (name, category, price, stock) VALUES
('Laptop','tech',900,5), ('Mouse','tech',20,50), ('Desk','office',300,0),
('Lamp','office',40,12), ('Pen','office',2,0);
CREATE TABLE price_updates (product_id INT, new_price REAL);
INSERT INTO price_updates VALUES (1, 850), (4, 45), (9, 99);
Note that price_updates includes product 9, which does not exist, a common situation with imported files.
Updating one row
UPDATE products SET price = 25 WHERE name = 'Mouse';
-- rowcount 1
Filtering by a unique key such as id is safer than by name, because names are rarely guaranteed to be unique. Always look at the row count: if you expected 1 and got 0 or 7, stop and investigate.
Updating several rows and columns
UPDATE products
SET price = ROUND(price * 1.10, 2),
updated_at = '2026-11-22'
WHERE category = 'office';
-- rowcount 3
-- Desk 330.0 2026-11-22 | Lamp 44.0 2026-11-22 | Pen 2.2 2026-11-22
One statement raised every office price by 10 percent and stamped the date. Expressions on the right of SET can use the row’s current values. ROUND keeps prices tidy, though for real money use a DECIMAL column rather than floating point.
Guarded updates: checking conditions atomically
UPDATE products SET stock = stock - 1 WHERE name = 'Laptop' AND stock > 0;
-- rowcount 1
UPDATE products SET stock = stock - 1 WHERE name = 'Desk' AND stock > 0;
-- rowcount 0 (out of stock, nothing changed)
Putting the business rule in the WHERE clause makes the check and the change one atomic step. Reading the stock first and then updating in a second statement allows two buyers to take the last item at the same moment. With the guard, the second buyer simply gets a row count of 0 and your code can say “sold out”. This pattern also implements optimistic locking, for example WHERE id = 7 AND version = 3. Our SQL transactions guide covers the concurrency background.
Updating different rows differently with CASE
UPDATE products
SET category = CASE WHEN price >= 100 THEN 'premium' ELSE category END;
-- Laptop premium | Mouse tech | Desk premium | Lamp office | Pen office
A CASE expression lets one statement apply different values per row. The ELSE category part is essential: without it, rows that do not match get NULL. Preview the logic first with the same CASE in a SELECT. See SQL CASE WHEN for more patterns.
UPDATE with JOIN: values from another table
UPDATE products
SET price = pu.new_price
FROM price_updates AS pu
WHERE pu.product_id = products.id;
-- rowcount 2
-- 1 Laptop 850.0 | 2 Mouse 20.0 | 3 Desk 300.0 | 4 Lamp 45.0 | 5 Pen 2.0
The UPDATE ... FROM form joins the target table to another table and updates only matching rows. Product 9 in the update file matched nothing and was safely ignored, and products without an update kept their prices. SQLite has supported this since version 3.33; PostgreSQL uses the same syntax.
UPDATE JOIN syntax by database
-- MySQL / MariaDB
UPDATE products p
JOIN price_updates pu ON pu.product_id = p.id
SET p.price = pu.new_price;
-- SQL Server
UPDATE p
SET p.price = pu.new_price
FROM products p
JOIN price_updates pu ON pu.product_id = p.id;
-- PostgreSQL and SQLite 3.33+
UPDATE products SET price = pu.new_price
FROM price_updates pu WHERE pu.product_id = products.id;
Only the last form was run for this article. If several rows in the source table match one target row, the result is unpredictable in most databases, so de-duplicate the source first, for example with GROUP BY or ROW_NUMBER. Oracle uses MERGE or an updatable join view instead. For insert-or-update logic, see SQL upsert.
The correlated subquery NULL trap
The older portable way to update from another table is a correlated subquery. Written carelessly, it destroys data:
UPDATE products
SET price = (SELECT new_price FROM price_updates
WHERE product_id = products.id);
-- rowcount 5
-- 1 Laptop 850.0 | 2 Mouse None | 3 Desk None | 4 Lamp 45.0 | 5 Pen None
Five rows changed, not two. For products with no matching update, the subquery returned no row, which SQL treats as NULL, so Mouse, Desk and Pen lost their prices. The fix is to restrict the update to rows that have a match:
UPDATE products
SET price = (SELECT new_price FROM price_updates WHERE product_id = products.id)
WHERE EXISTS (SELECT 1 FROM price_updates WHERE product_id = products.id);
-- rowcount 2 (correct)
Our EXISTS vs IN guide explains the EXISTS check. Wrapping the subquery in COALESCE(..., price) also protects the old value, but it still rewrites every row, which can fire triggers and bloat logs.
Tip: whenever SET uses a subquery, add a WHERE EXISTS with the same condition, then confirm that the row count equals the number of expected matches.
All SET expressions see the old row
UPDATE products SET price = price * 2, stock = price;
-- Laptop price 1800.0, stock 900 | Mouse price 40.0, stock 20
The stock = price assignment used the price before doubling. In standard SQL, PostgreSQL, SQLite and SQL Server, every expression in SET reads the original row, so you can swap two columns with SET a = b, b = a. MySQL is a well-known exception: it evaluates assignments left to right, so the second expression would see the new value there.
RETURNING: see what changed
UPDATE products SET stock = stock + 10 WHERE stock = 0 RETURNING name, stock;
-- [('Desk', 10), ('Pen', 10)]
RETURNING gives you the updated rows straight away, without a second query. It is supported by PostgreSQL, SQLite 3.35+ and MariaDB; SQL Server uses an OUTPUT clause instead. It is perfect for logging what changed and for returning the new state to an API caller.
The safety net: preview, transaction, verify
BEGIN;
UPDATE products SET price = 0; -- forgot the WHERE clause
-- rowcount 5 (every row!)
ROLLBACK;
-- prices restored: Laptop 900.0, Mouse 20.0, Desk 300.0, Lamp 40.0, Pen 2.0
A transaction turned a disaster into a non-event. The safe routine for any manual update:
- Write the
SELECTwith the sameWHEREand check the rows. - Start a transaction with
BEGIN. - Run the
UPDATEand compare the row count with theSELECTcount. - Spot-check the result, then
COMMIT, orROLLBACKif anything looks wrong.
MySQL’s --safe-updates client mode refuses updates without a key-based WHERE or LIMIT, a useful guard for interactive sessions. Backups still matter: a transaction cannot help after you commit.
Constraints still apply
UPDATE u SET email = 'a@example.com' WHERE id = 2;
-- sqlite3.IntegrityError: UNIQUE constraint failed: u.email
Unique, foreign key, check and NOT NULL constraints are enforced on updates just as on inserts, and the failing statement changes nothing. That is a strong reason to define them; see SQL constraints. Triggers also fire on updates, which can be used for audit columns; see SQL triggers.
Updating millions of rows
- Index the columns in
WHEREand join conditions, or every update scans the whole table. - Update in batches, for example 10,000 rows at a time by key range, to keep locks and transaction logs small.
- Skip rows that already have the target value:
WHERE status <> 'archived'. - Schedule big updates outside peak hours and watch replication lag.
SQL UPDATE checklist
- Write and run the matching
SELECTfirst. - Always include a
WHEREclause, preferably on a key. - Use guard conditions for business rules and check the row count.
- Pair subquery-based
SETwithWHERE EXISTS. - De-duplicate the source table before an update join.
- Use a transaction and
RETURNINGorOUTPUTfor manual changes.
Final thoughts
The SQL UPDATE statement is short to write and easy to get wrong. Restrict it with a precise WHERE, check the affected row count, use joins or EXISTS when pulling values from another table, and remember that every SET expression sees the old row. Wrap manual changes in a transaction and your mistakes stay reversible. When you need to remove rows instead, see DELETE vs TRUNCATE vs DROP.
Frequently asked questions
What happens if I run UPDATE without WHERE?
Every row in the table is updated. In our test all five products changed. Run updates inside a transaction so you can roll back, and preview the rows with a SELECT first.
How do I update a table using values from another table?
Use UPDATE with FROM in PostgreSQL and SQLite, UPDATE with JOIN in MySQL, or UPDATE with FROM and JOIN in SQL Server. Only rows with a match are changed.
Why did my UPDATE set values to NULL?
A correlated subquery in SET returns NULL for rows with no match, and without a WHERE clause every row is updated. Add WHERE EXISTS with the same condition so only matching rows change.
How do I update multiple columns at once?
List them in SET separated by commas, for example SET price = 10, stock = 5. All expressions read the original row values in most databases, MySQL being the main exception.
How can I see which rows an UPDATE changed?
Use a RETURNING clause in PostgreSQL, SQLite and MariaDB, or an OUTPUT clause in SQL Server. You can also check the affected row count reported by your database driver.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.