Updating Data in SQL Tables: A Comprehensive Guide

The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax: UPDATE Syntax

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

Prerequisite Codes

If you don't have table please create below table and practice it.

My database name is SQLExamples

USE SQLExamples

Create this below Employee table

DROP TABLE Employee
CREATE TABLE Employee(
EmpId INT,
EmpName VARCHAR(25),
EmpAddress VARCHAR(100),
EmpDept VARCHAR(25)
)

Insert data inside table

INSERT INTO Employee VALUES
(1, 'Rambo', 'Kolkata', 'IT'),
(2, 'Rohit', 'Kolkata', 'IT'),
(3, 'Rohon', 'Kolkata', 'ITIS'),
(4, 'Ronok', 'Kolkata', 'ITIS'),
(5, 'Rubin', 'Kolkata', 'ITIS'),
(6, 'Sorif', 'Kolkata', 'ADMIN'),
(7, 'Soriful', 'Kolkata', 'ADMIN'),
(8, 'Sofik', 'Kolkata', 'ADMIN')

SELECT * FROM Employee

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Kolkata	IT
2	Rohit	Kolkata	IT
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example 1:

Update Employee Address where employee department is IT

Code:

UPDATE Employee
SET EmpAddress = 'Mumbai'
WHERE  EmpDept = 'IT'

Output:

The above code will produce the following result-

(2 row(s) affected) 

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Mumbai	IT
2	Rohit	Mumbai	IT
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example: 2

Update employee name and address where employee Id is 1

Code:

UPDATE Employee
SET EmpName = 'Ismile', EmpAddress= 'Kerala'
WHERE EmpId = 1

Output:

The above code will produce the following result-

(1 row(s) affected)

EmpId	EmpName	EmpAddress	EmpDept
1	Ismile	Kerala	IT
2	Rohit	Mumbai	IT
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example 3: Update Multiple Records

Code:

UPDATE Employee
SET EmpAddress='Hydrabad'
WHERE EmpAddress='Kolkata';

Output:

The above code will produce the following result-

(6 row(s) affected)

EmpId	EmpName	EmpAddress	EmpDept
1	Ismile	Kerala	IT
2	Rohit	Mumbai	IT
3	Rohon	Hydrabad	ITIS
4	Ronok	Hydrabad	ITIS
5	Rubin	Hydrabad	ITIS
6	Sorif	Hydrabad	ADMIN
7	Soriful	Hydrabad	ADMIN
8	Sofik	Hydrabad	ADMIN

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Syntax:

 UPDATE Employee
SET EmpName='Rambo Azmi'

Output:

All the employee name will change to "Rambo Azmi"-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo Azmi	Kerala	IT
2	Rambo Azmi	Mumbai	IT
3	Rambo Azmi	Hydrabad	ITIS
4	Rambo Azmi	Hydrabad	ITIS
5	Rambo Azmi	Hydrabad	ITIS
6	Rambo Azmi	Hydrabad	ADMIN
7	Rambo Azmi	Hydrabad	ADMIN
8	Rambo Azmi	Hydrabad	ADMIN