SQL UPDATE - Guide to Modifying Database Records

UPDATE modifies existing rows in a table. You specify which columns to change with SET and which rows to target with WHERE. Unlike INSERT (which adds rows) or DELETE (which removes them), UPDATE changes values in rows that already exist rather than adding or removing rows. Logically it is still the same row, although even its primary key can be updated; physically, engines differ. PostgreSQL writes a new version of the row under its multiversion concurrency control, and Delta Lake either rewrites the data files that hold changed rows or, with deletion vectors, marks those rows in metadata and rewrites the files later.

This guide covers single-row and multi-row updates, UPDATE with JOIN, conditional updates using CASE, and the safety patterns that prevent accidental mass modification.

UPDATE Syntax

The basic UPDATE statement has three parts - the table, the SET clause, and the WHERE clause:

UPDATE employees SET salary = 72000 WHERE employee_id = 1001;

You can update multiple columns in a single statement:

UPDATE products SET price = 29.99, stock_quantity = 150, last_modified = CURRENT_TIMESTAMP WHERE product_code = 'ABC123';

Column values in the SET clause can reference other columns in the same row. This runs the calculation per row, using the row's current values:

UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

Without a WHERE clause, UPDATE modifies every row in the table. This is rarely intentional. Standard SQL allows it because there are legitimate use cases (resetting a flag on all rows, for example), and a forgotten WHERE clause is a classic cause of accidental mass changes. MySQL offers a guard rail: with the safe-updates mode of the mysql client (the sql_safe_updates variable), UPDATE and DELETE statements fail unless they identify rows by a key in WHERE, use LIMIT, or both, and MySQL Workbench enables an equivalent Safe Updates preference by default.

UPDATE with WHERE Patterns

The WHERE clause controls which rows are modified. Write it once, test it with SELECT, then reuse it in the UPDATE.

The pattern: write your WHERE clause, run SELECT * FROM table WHERE ... to see exactly which rows will change, verify the count and the values, then swap SELECT for UPDATE.

-- Step 1: verify which rows match SELECT employee_id, first_name, salary FROM employees WHERE department = 'Sales' AND hire_date < '2024-01-01'; -- Step 2: if the results look correct, update UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales' AND hire_date < '2024-01-01';

Common WHERE patterns with UPDATE:

-- Update by primary key (single row) UPDATE customers SET email = 'new@example.com' WHERE customer_id = 42; -- Update with IN (specific set of rows) UPDATE products SET discontinued = true WHERE product_id IN (101, 205, 318); -- Update with BETWEEN UPDATE orders SET priority = 'urgent' WHERE order_date BETWEEN '2025-12-20' AND '2025-12-31'; -- Update NULLs UPDATE employees SET phone = 'not provided' WHERE phone IS NULL;

UPDATE with JOIN

UPDATE with JOIN modifies rows in one table based on data from another. The syntax differs across databases.

All three do the same thing: increase salaries by 10% for employees in the Sales department. The structural difference is where the join condition lives (MySQL, PostgreSQL, SQL Server). Snowflake also accepts UPDATE ... FROM, and Oracle's current SQL reference documents a similar direct-join UPDATE ... FROM; on older Oracle releases, use MERGE or a subquery.

A join-based update should match at most one source row per target row. If a target row matches several, PostgreSQL uses one of them and which one is not predictable, Snowflake behaves the same way by default (its ERROR_ON_NONDETERMINISTIC_UPDATE parameter turns that into an error), Oracle raises ORA-30926, and MySQL updates each matching row only once, without documenting which of the matching source rows supplies the value. Deduplicate or aggregate the source first.

For a portable alternative, filter with a subquery in WHERE, which is standard SQL:

UPDATE employees SET salary = salary * 1.1 WHERE dept_id IN ( SELECT dept_id FROM departments WHERE name = 'Sales' );

When the new value itself comes from another table, use a correlated subquery in SET and repeat the match in WHERE with EXISTS. Without that WHERE, rows with no match are set to NULL, and the subquery must return at most one row per target row:

UPDATE products SET price = (SELECT n.new_price FROM price_list n WHERE n.product_id = products.product_id) WHERE EXISTS (SELECT 1 FROM price_list n WHERE n.product_id = products.product_id);

MERGE for Upserts

MERGE, added to the SQL standard in SQL:2003, combines update and insert logic in one statement: target rows that match the source are updated, and source rows with no match can be inserted (most engines also allow a delete branch). It is available in SQL Server, Oracle, PostgreSQL since version 15, Snowflake and Databricks (MERGE INTO on Delta tables). MySQL has no MERGE; its closest equivalent is INSERT ... ON DUPLICATE KEY UPDATE.

MERGE INTO products t USING price_list s ON (t.product_id = s.product_id) WHEN MATCHED THEN UPDATE SET price = s.new_price WHEN NOT MATCHED THEN INSERT (product_id, price) VALUES (s.product_id, s.new_price);

Like a join-based UPDATE, MERGE expects each target row to match at most one source row, and most engines raise an error when one matches more than once.

Conditional UPDATE with CASE

CASE in the SET clause lets you apply different values to different rows in a single pass. This replaces multiple UPDATE statements with one:

UPDATE employees SET bonus = CASE WHEN performance_rating >= 4.5 THEN salary * 0.15 WHEN performance_rating >= 3.5 THEN salary * 0.10 WHEN performance_rating >= 2.5 THEN salary * 0.05 ELSE 0 END WHERE review_year = 2025;

Include an ELSE so the fallback is explicit.

Other practical CASE update patterns:

-- Status transitions based on date (PostgreSQL interval syntax; -- MySQL writes CURRENT_DATE + INTERVAL 30 DAY) UPDATE subscriptions SET status = CASE WHEN end_date < CURRENT_DATE THEN 'expired' WHEN end_date < CURRENT_DATE + INTERVAL '30 days' THEN 'expiring_soon' ELSE status END WHERE end_date < CURRENT_DATE + INTERVAL '30 days'; -- Data normalization in one pass (SQL Server uses LEN instead of LENGTH) UPDATE addresses SET state = UPPER(TRIM(state)) WHERE LENGTH(TRIM(state)) = 2;

Transaction Safety

Wrap UPDATE statements in transactions whenever the consequences of a wrong change are expensive. The pattern is simple: BEGIN, UPDATE, verify, then COMMIT or ROLLBACK.

-- PostgreSQL and MySQL; SQL Server uses BEGIN TRANSACTION BEGIN; UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 'PROD001'; -- Check the result before committing SELECT product_id, quantity FROM inventory WHERE product_id = 'PROD001'; -- If the quantity looks correct: COMMIT; -- If something is wrong: -- ROLLBACK;

Without an explicit transaction, PostgreSQL, MySQL and SQL Server commit each statement on its own (autocommit), so there is nothing to roll back once the UPDATE finishes. Oracle Database instead starts a transaction implicitly with the first DML statement and keeps it open until COMMIT or ROLLBACK. While a transaction is open, the updated rows stay locked against other writers, so keep the verify step short rather than leaving the session idle.

To record when a row last changed, set a timestamp column in the same statement (PostgreSQL syntax below):

UPDATE user_sessions SET last_activity = NOW(), session_duration = EXTRACT(EPOCH FROM NOW() - login_time) WHERE session_id = 'abc123';

For a dependable audit trail, a trigger is safer than relying on every statement to set the column. In PostgreSQL, BEFORE UPDATE and AFTER UPDATE triggers can run once per modified row or once per statement and can write the old and new values to a history table; SQL Server (statement-level) and MySQL (row-level) offer UPDATE triggers too.

Before running UPDATE statements on production tables, it helps to know which downstream reports and dashboards read the data. Data lineage shows these dependencies. In Dawiso's catalog, lineage is built from supported connectors, dbt, SQL parsing (not on by default) and manually added relations, so it shows the reports a table feeds as far as those sources cover the pipeline, and teams can use it to warn the owners of affected reports before a bulk change.

Batch Updates for Large Tables

Updating millions of rows in a single statement is dangerous. The database holds locks on all affected rows until the transaction commits. On busy tables, this means other writers that touch those rows queue up behind the locks, the transaction log (or undo and write-ahead log) grows to hold the change, and replicas fall behind.

The solution is batching. Process a fixed number of rows, commit, then repeat:

-- MySQL: batch with LIMIT UPDATE orders SET status = 'archived' WHERE order_date < '2023-01-01' AND status = 'completed' LIMIT 5000; -- Repeat until 0 rows affected -- SQL Server: batch with TOP UPDATE TOP(5000) orders SET status = 'archived' WHERE order_date < '2023-01-01' AND status = 'completed'; -- Repeat until @@ROWCOUNT = 0 -- PostgreSQL: batch with a CTE, each batch in its own transaction WITH batch AS ( SELECT order_id FROM orders WHERE order_date < '2023-01-01' AND status = 'completed' LIMIT 5000 ) UPDATE orders SET status = 'archived' WHERE order_id IN (SELECT order_id FROM batch);

Each batch must shrink the remaining set, or the loop never ends: here the WHERE only matches rows still marked 'completed', so archived rows drop out. Between batches, the database releases locks, log space can be reused once it is no longer needed (after a checkpoint or log backup, depending on the engine), and replicas catch up. For very large updates during business hours, add a short delay between batches to reduce load.

Database-Specific Features

PostgreSQL supports UPDATE ... RETURNING, which gives you back the modified rows without a separate SELECT. Since PostgreSQL 18, RETURNING can also return the old values alongside the new ones, and SQLite 3.35+ supports RETURNING as well:

UPDATE employees SET salary = salary * 1.05 WHERE department = 'Engineering' RETURNING employee_id, first_name, salary;

MySQL supports UPDATE ... ORDER BY ... LIMIT on single-table updates, useful for processing the oldest or newest rows first:

UPDATE support_tickets SET priority = 'high' WHERE status = 'open' AND created_at < '2025-01-01' ORDER BY created_at ASC LIMIT 100;

SQL Server provides the OUTPUT clause (similar to PostgreSQL's RETURNING), which can return the new values (INSERTED) and the old ones (DELETED), and supports UPDATE ... FROM ... JOIN syntax:

UPDATE employees SET salary = salary * 1.05 OUTPUT INSERTED.employee_id, DELETED.salary AS old_salary, INSERTED.salary WHERE department = 'Engineering';

Snowflake supports join-based UPDATE ... FROM and MERGE, and its Time Travel lets you query or restore a table as it was before a bad update, within the table's retention period.

Databricks (Delta Lake) supports UPDATE and MERGE INTO on Delta tables. Delta Lake does not edit its Parquet files in place: without deletion vectors, modifying a single row rewrites the whole file that contains it, and with deletion vectors enabled, changed rows are marked in metadata and the files are rewritten later, for example by OPTIMIZE. Delta time travel can read an earlier version of the table.

Common Mistakes

Missing WHERE clause. The most destructive mistake. UPDATE employees SET salary = 0 without WHERE sets every salary to zero. Once it is committed, recovery means a point-in-time restore from backup, or a time-travel query on platforms that offer one (Snowflake, Delta Lake). Prevention: always use the SELECT-first pattern, and run production updates inside a transaction.

Relying on implicit type conversion in SET values. A quoted ISO 8601 value such as '2025-01-15' is read the same way in any session for a DATE column in PostgreSQL, MySQL and SQL Server (SQL Server's older datetime type is the exception: there it follows DATEFORMAT, and '20250115' is the safe form). Other formats such as '15/01/2025' are read according to session settings in PostgreSQL (DateStyle) and SQL Server (DATEFORMAT and language), while MySQL always expects year-month-day order, so the same statement can store a different date or fail. Use ISO 8601 values, the standard DATE '2025-01-15' literal where the engine supports it, or parameterized queries.

Deadlocks from concurrent updates. Two sessions updating overlapping sets of rows in different order can deadlock. The fix: always update rows in a consistent order (by primary key), and keep transactions short.

Updating a column used in the WHERE clause. If your WHERE clause filters on status = 'pending' and your SET clause changes status to 'processed', the statement still updates each matching row once: the WHERE is evaluated against the row as it was before this statement changed it. Concurrency is where it gets subtle. In PostgreSQL's default READ COMMITTED level, if another transaction changes a matching row first, the UPDATE waits for it and then re-evaluates its WHERE clause against the new version of the row, skipping the row if it no longer matches.

Assuming SET assignments always see the old values. In standard SQL, PostgreSQL and SQL Server, every expression in SET reads the row as it was before the update, so SET a = b, b = a swaps two columns. MySQL generally evaluates single-table assignments from left to right (and guarantees no order for multiple-table updates), so the same statement sets both columns to the old value of b.

Sources

• PostgreSQL - UPDATE
• PostgreSQL - MERGE
• PostgreSQL - PostgreSQL 15 release notes
• PostgreSQL - PostgreSQL 18 release notes
• PostgreSQL - Heap-Only Tuples (HOT)
• PostgreSQL - Transaction Isolation
• PostgreSQL - Transactions tutorial
• PostgreSQL - CREATE TRIGGER
• PostgreSQL - Conditional Expressions: CASE
• MySQL - UPDATE Statement
• MySQL - Using Safe-Updates Mode
• MySQL - Workbench SQL Editor Preferences
• MySQL - INSERT ... ON DUPLICATE KEY UPDATE
• Microsoft - UPDATE (Transact-SQL)
• Microsoft - OUTPUT clause (Transact-SQL)
• Microsoft - MERGE (Transact-SQL)
• Microsoft - Transaction locking and row versioning guide
• Microsoft - datetime (Transact-SQL)
• Oracle - UPDATE
• Oracle - MERGE
• Oracle - Transactions
• Snowflake - UPDATE
• Snowflake - MERGE
• Snowflake - Understanding and using Time Travel
• Databricks - UPDATE (Delta Lake)
• Databricks - MERGE INTO
• Databricks - Deletion vectors
• Databricks - Delta table history and time travel
• SQLite - RETURNING clause
• Dawiso Help - Sources of data lineage