SQL UPDATE Statement: Modify Existing Rows Safely (Examples)

SQL Tutorial · Chapter 26 of 32

The SQL UPDATE statement changes values in rows that already exist in a table. You name the table, list the columns and their new values after SET, and use WHERE to choose which rows change. It is one of the most useful commands in SQL, and also one of the riskiest: an UPDATE without a WHERE clause changes every row in the table.

This chapter is for beginners and analysts who already know how to read data with SELECT and now need to modify it. You will learn the syntax, five worked examples on a small Orders table (one row, several columns, calculations, subqueries and joins), how the syntax differs across MySQL, SQL Server, PostgreSQL and SQLite, the most common mistakes and how to fix them, and a safe routine you can use on real data.

SQL UPDATE Syntax

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

The golden rule: write a SELECT with the exact same WHERE clause first, check the rows it returns, then turn it into an UPDATE.

Sample Tables (Before)

All examples use this Orders table. Each example builds on the previous one.

Examples 4 and 5 also use a small Customers table. Note that customer 6 (who placed order 105) has no row here.

Example 1: Update One Column in One Row

UPDATE Orders
SET Amount = 30
WHERE OrderID = 102;

SELECT OrderID, Product, Amount FROM Orders WHERE OrderID = 102;

Filtering on the primary key (OrderID) guarantees that exactly one row changes. The database reports “1 row affected”.

Example 2: Update Several Columns at Once

UPDATE Orders
SET Product = 'Gaming Laptop', Amount = 1300
WHERE OrderID = 106;

SELECT OrderID, Product, Amount FROM Orders WHERE OrderID = 106;

Column = value pairs are separated by commas. Text values go in single quotes.

Example 3: Update Many Rows with a Calculation

Apply a 10% price increase to every laptop. The expression on the right uses each row’s own current Amount.

UPDATE Orders
SET Amount = Amount * 1.10
WHERE Product LIKE '%Laptop%';

SELECT OrderID, Product, Amount FROM Orders WHERE Product LIKE '%Laptop%';

Two rows match the pattern, so two rows change: 1200 becomes 1320 and 1300 (from Example 2) becomes 1430.

Example 4: Update Using a Subquery

Mark every customer who has placed an order over 1000 as VIP in the Customers table’s Segment column. The subquery finds the qualifying customer IDs in Orders.

UPDATE Customers
SET Segment = 'VIP'
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE Amount > 1000);

SELECT CustomerID, Name, Segment FROM Customers WHERE Segment = 'VIP';

Orders 101 (customer 1, now 1320) and 106 (customer 4, now 1430) are the only ones above 1000, so only those two customers change. The other three stay Regular.

Example 5: Update from Another Table (Join)

Copy each customer’s Country into an Orders column called ShipCountry. Assume the column was added beforehand with ALTER TABLE Orders ADD ShipCountry VARCHAR(50);, so it starts as NULL for every order. This is where the databases differ, so all three common forms are shown.

-- MySQL
UPDATE Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
SET o.ShipCountry = c.Country;

-- SQL Server
UPDATE o SET o.ShipCountry = c.Country
FROM Orders o JOIN Customers c ON c.CustomerID = o.CustomerID;

-- PostgreSQL / SQLite
UPDATE Orders SET ShipCountry = c.Country
FROM Customers c WHERE c.CustomerID = Orders.CustomerID;

Order 105 stays NULL because customer 6 has no row in Customers to copy from. An inner join simply skips rows with no match.

More Useful UPDATE Patterns

• Set a value to NULL: UPDATE Orders SET ShipCountry = NULL WHERE OrderID = 105;
• Conditional values with CASE: UPDATE Orders SET Amount = CASE WHEN Amount < 50 THEN Amount + 5 ELSE Amount END;
• Update by date range: UPDATE Orders SET Amount = Amount * 0.95 WHERE OrderDate BETWEEN '2026-01-01' AND '2026-01-31';
• See changed rows immediately (PostgreSQL and SQLite): add RETURNING * to the end of the statement.

A Safe Routine for Real Data

1. Run SELECT * FROM Orders WHERE ... with the exact WHERE clause and note the row count.
2. Start a transaction: BEGIN; (or START TRANSACTION; in MySQL, BEGIN TRANSACTION; in SQL Server).
3. Run the UPDATE and check that “rows affected” matches the count from step 1.
4. Run the SELECT again to inspect the new values.
5. If everything looks right, COMMIT;. If not, ROLLBACK; and nothing is changed.
6. On production systems, take a backup first and prefer a WHERE on the primary key.

Works in MySQL, SQL Server, PostgreSQL and SQLite

MySQL Workbench runs in safe update mode by default and rejects an UPDATE whose WHERE does not use a key column. It can feel annoying, but it is a useful guard rail while you learn.

Common Mistakes and How to Fix Them

Practice Exercise

1. Change the OrderDate of order 104 to 2026-02-03.
2. Reduce every Amount below 50 by 5.

Answers

-- 1
UPDATE Orders SET OrderDate = '2026-02-03' WHERE OrderID = 104;

-- 2 (check first: SELECT * FROM Orders WHERE Amount < 50;)
UPDATE Orders SET Amount = Amount - 5 WHERE Amount < 50;
-- affects orders 102 (30 becomes 25) and 104 (45 becomes 40)

Related SQL Chapters

• SQL INSERT INTO: adding new rows before you update them
• SQL DELETE: removing rows, with the same WHERE safety rules
• SQL WHERE clause: the filter that keeps UPDATE safe
• SQL LIKE operator: the pattern matching used in Example 3
• SQL IN operator: the list filter used with the subquery in Example 4
• SQL INNER JOIN: how the join in Example 5 matches rows

To practise the same update-and-calculate habits inside a spreadsheet, try the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy, which includes practice files.

Frequently Asked Questions

What happens if I run UPDATE without a WHERE clause?

Every row in the table receives the new value. There is no confirmation prompt, so always test the WHERE clause with a SELECT first and work inside a transaction.

Can I update multiple columns in one statement?

Yes. List each column = value pair after SET, separated by commas, for example SET Product = 'X', Amount = 100.

How do I undo an UPDATE?

If it ran inside a transaction that has not been committed, run ROLLBACK. Once committed, you need a backup or a reverse UPDATE that puts the old values back.

Can I update a table using values from another table?

Yes. Use a join (MySQL: UPDATE … JOIN; SQL Server: UPDATE … FROM … JOIN; PostgreSQL and SQLite: UPDATE … FROM) or a correlated subquery in SET.

How do I know how many rows an UPDATE changed?

The database returns a “rows affected” count after the statement runs. Compare it with the row count from your test SELECT.

What is the difference between UPDATE and ALTER TABLE?

UPDATE changes the data stored in rows. ALTER TABLE changes the structure of the table, such as adding or renaming a column.

Wrapping Up

The SQL UPDATE statement comes down to three parts: the table, the SET list and the WHERE filter. Get the WHERE right with a test SELECT, wrap real changes in a transaction, and check the rows-affected count before you commit. Try the practice exercise on your own copy of the Orders table, then move on to the SQL DELETE chapter, which uses the same safety habits.

📅 Last updated: October 2026

Chapter 26 of 32 · SQL from Scratch: all 32 chapters