SQL Update – Mastering SQL Update with Examples
Dive deep into one of the most commonly used SQL commands - UPDATE. It is used to modify existing records in a table and is a crucial part of database management and maintenance.
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
Related Course
Master SQL — Hands-On
Join 5,000+ students at edu.machinelearningplus.com
Explore Course
Related Articles
