How To Update Table Data in SQL Server

Update statement is used to update records in a table. We can update a single or multiple records using WHERE clause. We can also update all the records if we omit the WHERE clause.

Update statement can change all the values if we omit WHERE clause.

For example, If we want to update the age of a particular employee and by mistake if we omit the WHERE clause, then age of all the employee’s gets affected.

Syntax:

UPDATE tableName SET columnName=Value
WHERE condtion;

First, we create a table tblEmployee and do some practical example with UPDATE statement.

CREATE TABLE tblEmployee
(
	EmpID INT PRIMARY KEY NOT NULL,
	EmpFirstName NVARCHAR(20) NOT NULL,
	EmpLastName NVARCHAR(20) NOT NULL,
	EmpAge INT NULL,
	EmpGender NVARCHAR(10) NULL,
	EmpEmail NVARCHAR(50) NULL,
	EmpSalary INT NULL,
	EmpJoiningDate DATETIME NULL
)
GO

INSERT INTO tblEmployee
    (EmpID, EmpFirstName,EmpLastName,EmpAge,EmpGender,EmpEmail,EmpSalary,EmpJoiningDate)
VALUES
    (1,'john','dave',26,'male','john@gmail.com',26000,'2010-02-01'),
    (2,'kamran','akmal',35,'male','kami@gmail.com',25000,'2011-01-01'),
    (3,'umar','akmal',36,'male','umar@gmail.com',38000,'2015-01-01'),
    (4,'thomas','hardy',36,'male','thomas@gmail.com',25000,'2016-05-05'),
    (5,'amar','sidhu',36,'male','amar@gmail.com',25000,'2016-05-05'),
    (6,'simran','sidhu',25,'female','simran@gmail.com',33000,'2018-01-01'),
    (7,'diyana','jeni',26,'female','diyana@gmail.com',50000,'2005-01-01'),
    (8,'maria','anders',27,'female','maria@gmail.com',70000,'2004-01-01'),
    (9,'anam','chaudhary',29,'female','anam@gmail.com',25000,'2020-01-01'),
    (10,'Amelia','sidhu',56,'female','amelia@gmail.com',50000,'2015-01-01')
    GO

UPDATE Single Column Value in SQL

We will update EmpFirstName of EmpID 1 from John to Johnson using WHERE clause.

UPDATE tblEmployee SET EmpFirstName = 'Johnson'
WHERE EmpID = 1;

See the result of above UPDATE statement as shown in below image.

UPDATE Multiple Column Values in SQL

Now, we will update multiple column values using WHERE clause. Here we will update EmpFirstName, EmpLastName, and EmpEmail of Employee whose EmpID is 2.

UPDATE tblEmployee SET EmpFirstName = 'Martin',EmpLastName='Soomer',EmpEmail='martin@gmail.com'
WHERE EmpID = 2;

UPDATE Multiple Rows in SQL

We can update multiple rows using WHERE clause. Suppose we want to update the Salary of male employees equal to 40000, then we update the EmpSalary column as…

UPDATE tblEmployee SET EmpSalary = 40000
WHERE EmpID = 2;

The result of the above UPDATE statement is shown below.

Be conscious, while updating your records. For Example, if you want to change the age of an employee whose EmpID is 1, and you forgot to apply the WHERE clause, then the age of all employees gets affected.