Next Chapter »

SQL Tutorial - Update Statement


The 'UPDATE' statement is a DML statement. It is used to update a single/multiple record(s) into a table.

Here the 'UPDATE' statement is executed on the 'Employees' table.

The UPDATE Statement


The 'UPDATE' statement helps you update data in a table using the where clause.

SQL 'UPDATE ' Statement Syntax


UPDATE tablename
set (column-1 = value1, column-2 =value-2, ... column-n=value-n)
where condition;

The UPDATE Statement Example


The 'Employees' tables contains the following columns and their respective data type as shown below.

Table Name :

 Employees

Column Name Data Type  
EmployeeID smallint Not Null
EmployeeName varchar(50) Not Null
DateOfBirth smalldatetime Not Null
DesignationID smallint Allows Null
DeptID smallint Allows Null
PhoneNo nvarchar(12) Allows Null
City nvarchar(50) Allows Null

The 'UPDATE' statement updates all the specified data in the 'Employees' table as shown below provided the where condition is satisfied.

EmployeeID EmployeeName DateOfBirth DesignationID DeptID City
1 Richard Hughes 4/23/1945 12:00:00 AM 1 1 New Orleans
2 Taryn Sinclair 3/22/1980 12:00:00 AM 2 2 San Francisco
3 Ted Horowitz 3/31/1960 12:00:00 AM 3 4 New York
4 Jonathan Douglas 7/7/1971 12:00:00 AM 3 4 Salt Lake City
5 Miranda Leigh 7/19/1983 12:00:00 AM 7 3 New York
6 Jana Rae 9/23/1976 12:00:00 AM 8 2 Houston
7 Lita Rosanna 9/14/1982 12:00:00 AM 4 6 Long Island
8 Colin Flooks 12/29/1988 12:00:00 AM 4 2 Salt Lake City
9 Anthony Frank 2/22/1988 12:00:00 AM 9 3 San Francisco
10 Stephanie Lynn 7/30/1979 12:00:00 AM 8 2 Salt Lake City
11 Jack Martin 8/25/1985 12:00:00 AM NULL NULL Austin

UPDATE Employees
set EmployeeName='Melissa Hill', DateOfBirth='09/14/1991', DesignationID=3, DeptID=8, PhoneNo ='986-234-5341', City='San Jose'
where EmployeeID=7;

The above 'UPDATE" statement updates a single record in the 'Employees' table.The update will fail if value entered is not of the same datatype and also the where condition has to be satisfied. In the above SQL the EmployeeID given is 7.

Also note that when entering values for datatypes of char,varchar, nvarchar and date, enclose the values within ' ' such as 'Melissa Hill'. values of datatype int, smallint goes in as is without any quotes.


Next Chapter »