Data Update with UPDATE in SQL

In the previous article, we learned to create data with INSERT INTO. Now we are going to see how to modify it.

Because data is not static. People move house, products change price, and order statuses progress.

UPDATE is a powerful tool, but also one of the most dangerous in SQL. A poorly done INSERT only adds garbage. But a poorly done UPDATE can wipe out all your data irreparably.

Basic Syntax

The structure of an update statement has three key parts:

UPDATE TableName
SET Column = NewValue
WHERE Condition;

For example, imagine the customer with ID 105 calls us because they have changed their phone number.

UPDATE Customers
SET Phone = '600-123-456'
WHERE CustomerID = 105;

The Danger of Forgetting the WHERE

This is the most important advice we will give you in this module: Be careful when executing an UPDATE without WHERE.

If you forget the WHERE condition (copying from here to there, in a hurry, you don’t notice, you hit execute), SQL Server will update ALL the rows in the table.

-- ☠️ DANGER! This will set the same phone number for ALL your customers
UPDATE Customers
SET Phone = '600-123-456';

There is no “Are you sure?” message. You are going to delete all your data, destroy the planet, and get fired. No.

SQL Server will simply execute it and tell you: “5000 rows affected”. And you will be left with a bewildered look, not knowing what to do.

Updating Multiple Columns

We can modify several fields of the same row in a single statement, separating them by commas.

-- We update the customer's address and city
UPDATE Customers
SET Address = 'Calle Nueva 123',
    City = 'Barcelona',
    PostalCode = '08001'
WHERE CustomerID = 105;

Update with Expressions

We are not limited to setting fixed values (literals). We can use the current value of the column to calculate the new value. This is extremely useful for increments or percentage changes.

Example: Price increase due to inflation We want to increase the price of all products by 10%.

UPDATE Products
SET Price = Price * 1.10;
-- Here we intentionally do NOT use WHERE, because we want to affect all

Example: Stock correction There has been a sale and we subtract 5 units from the current stock.

UPDATE Inventory
SET Quantity = Quantity - 5
WHERE ProductID = 20;

UPDATE with JOIN (Exclusive to T-SQL)

Sometimes, the condition for updating a table depends on information that is in another table.

For example, we want to increase the price by 5%, but only for products in the “Electronics” category.

UPDATE P
SET P.Price = P.Price * 1.05
FROM Products P
INNER JOIN Categories C ON P.CategoryID = C.CategoryID
WHERE C.Name = 'Electronics';