SQL UPDATE Statement: Modify Existing Rows Safely (Examples)

SQL Tutorial · Chapter 26 of 32

The SQL UPDATE statement changes the values in existing rows of a table. You name the table, list the columns and their new values after SET, and use WHERE to say which rows to change. UPDATE without WHERE modifies every row, so the golden rule is: write the SELECT with the same WHERE first, check the rows, then run the UPDATE.

SQL UPDATE Syntax

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

Several columns can be changed in one statement, separated by commas. The new value can be a constant, an expression using the row’s current values, or a subquery.

Sample Table (Before)

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 guarantees exactly one row changes.

Example 2: Update Several Columns

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

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

Example 3: Update Many Rows with a Calculation

Apply a 10% price increase to every laptop. The expression 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%';

Example 4: Update Using a Subquery

Set the Country of every customer who has placed an order over 1000 to a VIP marker in a Segment column. The subquery finds the customer IDs.

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

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

Example 5: Update from Another Table (Join)

Copy each customer’s Country into a new Orders column called ShipCountry. The syntax differs by database; the MySQL form is shown, with the others below.

-- 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 to copy from.

Safety Checklist

1. Run SELECT * FROM Orders WHERE ... with the exact WHERE clause and confirm the row count.
2. Wrap the change in a transaction (BEGIN; UPDATE ...; check ; COMMIT; or ROLLBACK;).
3. Check the “rows affected” message the database returns.
4. On production data, back up first or use a WHERE on the primary key.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• Simple UPDATE … SET … WHERE is identical in all four.
• Multi-table UPDATE syntax differs, as shown in Example 5. SQLite supports UPDATE … FROM since version 3.33.
• MySQL Workbench runs in “safe update mode” by default and rejects an UPDATE without a key in WHERE; a useful guard rail.
• PostgreSQL and SQLite support RETURNING * to show the changed rows immediately.

Common Mistakes

• Forgetting WHERE and updating every row in the table.
• Writing SET Amount = 30 AND Product = 'Mouse'; multiple columns are separated by commas, not AND.
• Updating a text column with an unquoted value.
• Violating a constraint, e.g. setting a foreign key to a value that does not exist.

Practice Exercise

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

-- 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 and 104

Related Chapters

• SQL INSERT INTO – adding rows
• SQL DELETE – removing rows, with the same WHERE rules
• SQL WHERE – the clause that keeps UPDATE safe
• SQL from Scratch: full course overview

FAQ

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.

Can I update multiple columns in one statement?

Yes. List each column = value pair after SET, separated by commas: SET Product = ‘X’, Amount = 100.

How do I undo an UPDATE?

Only if it ran inside a transaction that has not been committed: issue ROLLBACK. Once committed, you need a backup or a reverse UPDATE with the old values.

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