SQL Update – Mastering SQL Update with Examples

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

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

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

And we’ll insert some data

INSERT INTO Employees (EmpID, EmpName, EmpEmail, Salary, Department)
VALUES 
    (1, 'John Doe', '[email protected]', 50000, 'HR'),
    (2, 'Jane Smith', '[email protected]', 60000, 'IT'),
    (3, 'Bob Johnson', '[email protected]', 70000, 'Sales'),
    (4, 'Alice Williams', '[email protected]', 55000, 'HR');

Our Employees table now looks like this

EmpID | EmpName       | EmpEmail             | Salary | Department
------|---------------|----------------------|--------|------------
1     | John Doe      | [email protected]     | 50000  | HR
2     | Jane Smith    | [email protected]   | 60000  | IT
3     | Bob Johnson   | [email protected] | 70000  | Sales
4     | Alice Williams| [email protected]| 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

UPDATE Employees
SET Salary = 65000
WHERE EmpID = 2;

Jane’s record in the Employees table would now be

EmpID | EmpName   | EmpEmail           | Salary | Department
------|-----------|--------------------|--------|------------
2     | Jane Smith| [email protected] | 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

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

Alice’s updated record

EmpID | EmpName      | EmpEmail              | Salary | Department
------|--------------|-----------------------|--------|------------
4     | Alice Williams | [email protected] | 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

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

The updated Employees table

EmpID | EmpName       | EmpEmail             | Salary | Department
------|---------------|----------------------|--------|------------
1     | John Doe      | [email protected]     | 55000  | HR
2     | Jane Smith    | [email protected]   | 65000  | IT
3     | Bob Johnson   | [email protected] | 70000  | Sales
4     | Alice Williams| [email protected]| 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.