1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL UPDATE statement: safe updates, UPDATE with JOIN and RETURNING

Key takeaways

  • The SQL UPDATE statement changes rows that match WHERE. Without WHERE, it changes every row; our test hit all 5.
  • Check the affected row count. A guarded WHERE stock > 0 update returned 0 rows for an out-of-stock item, which is how you detect failure.
  • Updating from a correlated subquery without a matching WHERE EXISTS set 3 of 5 prices to NULL.
  • Run risky updates inside a transaction, preview with SELECT first, and use RETURNING to 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:

  1. Write the SELECT with the same WHERE and check the rows.
  2. Start a transaction with BEGIN.
  3. Run the UPDATE and compare the row count with the SELECT count.
  4. Spot-check the result, then COMMIT, or ROLLBACK if 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 WHERE and 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 SELECT first.
  • Always include a WHERE clause, preferably on a key.
  • Use guard conditions for business rules and check the row count.
  • Pair subquery-based SET with WHERE EXISTS.
  • De-duplicate the source table before an update join.
  • Use a transaction and RETURNING or OUTPUT for 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
Software Development

301 vs 302 Redirect: Differences, SEO Impact, 307 and 308

301 vs 302 redirect: the differences, SEO impact, 307 and 308, method changes, redirect chains and loops, all tested on…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Databases & SQL

SQL Random Rows: How to Select Random Rows Fast and Fairly

SQL random rows: how to select random rows fast and fairly, timing ORDER BY RANDOM, offsets and ID jumps, and…

Read article →
Databases & SQL

SQL Median: 4 Ways to Calculate the Median, With Tested Queries

SQL median explained: four tested ways to calculate the median, including percentile functions, window ranks, offsets and per group medians.

Read article →
Software Development

JavaScript Regex Explained: Patterns, Flags, Groups and Pitfalls

JavaScript regex explained: literals, flags, groups, named groups, lookarounds, matchAll, replace and the catastrophic backtracking trap, tested in Node.

Read article →
Software Development

Python property: Getters, Setters, Validation and cached_property

Python property explained: getters, setters, deleters, validation, read-only and computed attributes, and cached_property, with tested code.

Read article →
Software Development

Python Enum Explained: auto, IntEnum, StrEnum, Flag and Best Practices

Python enum explained with tested examples: Enum, auto, IntEnum, StrEnum, Flag, aliases, unique, JSON serialisation and match statements.

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
Software Development

401 vs 403: Unauthorized vs Forbidden, and When to Use Each

401 vs 403 explained: authentication vs authorization, the WWW-Authenticate header, when to return 404 instead, client reactions and a tested…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →
Databases & SQL

SQL ROUND Function: Decimals, Ties, CEIL, FLOOR and Money

SQL ROUND explained with tested queries: decimals, tie rules, the 2.675 float trap, negative digits, CEIL, FLOOR, truncation and rounding…

Read article →
Databases & SQL

Recursive CTE in SQL: Hierarchies, Paths, Series and Cycles

Recursive CTE in SQL explained with tested queries: org charts with levels and paths, walking up a tree, team sizes,…

Read article →
Software Development

JavaScript Deep Copy: structuredClone vs JSON vs Spread, Tested

JavaScript deep copy compared: spread, Object.assign, the JSON trick, structuredClone and a custom copy, tested on Dates, Maps, Sets, BigInt…

Read article →
Artificial Intelligence

K Means Clustering Explained: How It Works, With Python Code

K means clustering explained: the algorithm, a from-scratch NumPy version, choosing k with the elbow and silhouette methods, k-means++, scaling…

Read article →
DevOps

Git Log Explained: 15 Useful Commands and Pretty Formats

Git log explained with 15 tested commands: oneline, graph, filters by author, date, message and code, file history, stats, pretty…

Read article →
Test Automation

Pytest conftest.py Explained: Shared Fixtures, Hooks and Overrides

Pytest conftest.py explained with a tested suite: shared fixtures, scope and teardown, folder overrides, autouse, custom options, markers and useful…

Read article →
Software Testing

Usability Testing: Methods, Metrics, SUS Score and Checklist

Usability testing explained: methods, planning, writing tasks, running sessions, metrics, SUS scoring, how many users you need and a practical…

Read article →
Databases & SQL

SQL UPDATE Statement: Safe Updates, UPDATE With JOIN and RETURNING

The SQL UPDATE statement explained with tested examples: WHERE guards, CASE updates, UPDATE with JOIN, the subquery NULL trap, RETURNING…

Read article →
Databases & SQL

SQL COUNT: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT) Explained

SQL COUNT explained: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT), NULLs and empty strings, conditional counts, HAVING, the LEFT JOIN trap and…

Read article →
Artificial Intelligence

K Nearest Neighbors (KNN) Explained: How It Works, With Python Code

K nearest neighbors explained: the KNN algorithm, a from-scratch NumPy version, choosing k, feature scaling, distance metrics, regression and vector…

Read article →
DevOps

Git Rebase Interactive: Squash, Fixup, Reword and Autosquash

Git rebase interactive explained step by step: pick, reword, squash, fixup, edit, drop, autosquash, conflicts and undoing a rebase with…

Read article →
Software Testing

Alpha vs Beta Testing: Key Differences, Exit Criteria and Checklist

Alpha vs beta testing compared: who tests, what each phase finds, entry and exit criteria, how to run a beta,…

Read article →
Test Automation

Pytest Monkeypatch: 10 Tested Examples for Env Vars, Time and APIs

Pytest monkeypatch explained with 10 passing tests: environment variables, functions, config, time, sleeps, paths, blocking the network and the wrong-target…

Read article →
Artificial Intelligence

Softmax Function Explained: Formula, Python Code and Stable Version

The softmax function explained: formula, worked example, NumPy code, the stable version that avoids NaN, temperature, cross-entropy and softmax vs…

Read article →
DevOps

Git Diff Explained: Staged Changes, Branches and Commits

Git diff explained: read the output, compare unstaged and staged changes, commits and branches, two dots vs three dots, and…

Read article →
Test Automation

Pytest Xdist: Run Tests in Parallel and Cut Test Time

Run pytest tests in parallel with pytest-xdist: install, the -n option, distribution modes, real timings and how to fix shared-state…

Read article →
Software Testing

Verification vs Validation in Software Testing: Example and Table

Verification vs validation in software testing explained with a comparison table and a tested coupon example where every spec test…

Read article →
Artificial Intelligence

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →