SQL Update – Mastering SQL Update with Examples

Written by Jagdeesh | 2 min read

Let’s dive deep into one of the most commonly used SQL commands – UPDATE. The UPDATE statement is used to modify existing records in a table and is a crucial part of database management and maintenance.

SQL UPDATE Syntax

The basic syntax for the SQL UPDATE command is as follows

sql

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

Now, let’s look at a few practical examples.

SQL UPDATE in Action

Sample Data

For our examples, let’s use a simple table named Employees

sql

CREATE TABLE Employees (
    EmpID INT,
    EmpName VARCHAR(50),
    EmpEmail VARCHAR(50),
    Salary FLOAT,
    Department VARCHAR(30)
);

And we’ll insert some data

sql

INSERT INTO Employees (EmpID, EmpName, EmpEmail, Salary, Department)
VALUES 
    (1, 'John Doe', 'jdoe@example.com', 50000, 'HR'),
    (2, 'Jane Smith', 'jsmith@example.com', 60000, 'IT'),
    (3, 'Bob Johnson', 'bjohnson@example.com', 70000, 'Sales'),
    (4, 'Alice Williams', 'awilliams@example.com', 55000, 'HR');

Our Employees table now looks like this

output

EmpID | EmpName       | EmpEmail             | Salary | Department
------|---------------|----------------------|--------|------------
1     | John Doe      | jdoe@example.com     | 50000  | HR
2     | Jane Smith    | jsmith@example.com   | 60000  | IT
3     | Bob Johnson   | bjohnson@example.com | 70000  | Sales
4     | Alice Williams| awilliams@example.com| 55000  | HR

1) Basic UPDATE

Jane Smith from the IT department has been given a raise, and her new salary is 65000.

Here’s how we would update her record

sql

UPDATE Employees
SET Salary = 65000
WHERE EmpID = 2;

Jane’s record in the Employees table would now be

output

EmpID | EmpName   | EmpEmail           | Salary | Department
------|-----------|--------------------|--------|------------
2     | Jane Smith| jsmith@example.com | 65000  | IT

2) Updating Multiple Columns

Alice Williams has been promoted and moved to the IT department with a new salary of 70000.

We can update her record like this

sql

UPDATE Employees
SET Salary = 70000, Department = 'IT'
WHERE EmpID = 4;

Alice’s updated record

output

EmpID | EmpName      | EmpEmail              | Salary | Department
------|--------------|-----------------------|--------|------------
4     | Alice Williams | awilliams@example.com | 70000  | IT

3) UPDATE With a Subquery

Suppose the company decides to give a 10% raise to everyone in the HR department.

Instead of updating each record individually, we can use a subquery

sql

UPDATE Employees
SET Salary = Salary * 1.1
WHERE Department = 'HR';

The updated Employees table

output

EmpID | EmpName       | EmpEmail             | Salary | Department
------|---------------|----------------------|--------|------------
1     | John Doe      | jdoe@example.com     | 55000  | HR
2     | Jane Smith    | jsmith@example.com   | 65000  | IT
3     | Bob Johnson   | bjohnson@example.com | 70000  | Sales
4     | Alice Williams| awilliams@example.com| 70000  | IT

Key Takeaways

Remember the following points when working with SQL UPDATE:

Always ensure you use a WHERE clause with your UPDATE statement.
– If you don’t, you’ll update all rows in the table.
– Be careful when using subqueries. They’re powerful but can also be tricky.
– Always backup your data before running an UPDATE statement. This will protect you from unintended changes.

Free Course

Master Core Python — Your First Step into AI/ML

Build a strong Python foundation with hands-on exercises designed for aspiring Data Scientists and AI/ML Engineers.

Start Free Course →

Trusted by 50,000+ learners