SQL UPDATE Statement

The SQL UPDATE statement is used to modify existing records in a table. It allows you to change one or more column values for specific rows using the WHERE clause. Without a WHERE condition, all rows in the table will be updated.

Example: First, we will create a demo SQL database and table, on which we will use the UPDATE Statement command.

Query:

UPDATE Employees
SET Salary = 65000
WHERE Name = 'Bob';  

Syntax:

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

• table_name: Name of the table you want to update.
• SET: The column(s) you want to update and their new values.
• WHERE: Filters the specific rows you want to update.

Note: The SET keyword assigns new values to columns, while the WHERE clause selects which rows to update. Without WHERE, all rows will be updated.

Examples of SQL UPDATE Statement

Let’s begin by creating a Customer table with some sample data. This table contains each customer's unique ID, name, last name, phone number and country. We will use it to demonstrate how the UPDATE statement works in SQL.

Query:

CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age INT,
Phone VARCHAR(15)
);

-- Insert sample data
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES

(1, 'Liam', 'Brown', 'United Kingdom', 25, '441234567890'),
(2, 'Sofia', 'Martinez', 'Spain', 23, '341234567890'),
(3, 'Akira', 'Tanaka', 'Japan', 26, '811234567890'),
(4, 'Hans', 'Müller', 'Germany', 27, '491234567890'),
(5, 'Olivia', 'Dubois', 'France', 24, '331234567890');

Output:

Example 1: Update Single Column Using UPDATE Statement

We have a Customer table and we want to Update the CustomerName where the Age is 23.

Query:

UPDATE Customer 
SET CustomerName = 'Isabella' 
WHERE Age = 23;

Output:

Explanation:

• The query updates the CustomerName to 'Isabella'
• It only affects the row where Age = 23
• Used to modify existing data in a specific record.

Example 2: Updating Multiple Columns using UPDATE Statement

We need to update both the CustomerName and Country for a specific CustomerID.

Query:

UPDATE Customer 
SET CustomerName = 'John', 
Country = 'Spain' 
WHERE CustomerID = 1;

Output:

Explanation:

• The query targets the row where CustomerID = 1.
• It updates CustomerName to 'John' and Country to 'Spain'.
• Both columns are updated simultaneously in a single SQL statement.

Note: For updating multiple columns we have used comma(,) to separate the names and values of two columns.

Example 3: Omitting WHERE Clause in UPDATE Statement

If we accidentally omit the WHERE clause, all the rows in the table will be updated, which is a common mistake. Let’s update the CustomerName for every record in the table:

Query:

UPDATE Customer 
SET CustomerName = 'ALice';

Output

Explanation:

• The query updates every row in the Customer table.
• It sets the CustomerName column to 'Alice' for all records.
• Since there is no WHERE clause, the change applies to the entire table.