Welcome to Lesson 5 of the TSQL Tutorial. In this lesson we will cover update statements. I hope you enjoy the lesson.
Select Statements
UPDATE
The UPDATE
statement is used to update or modify data in a table.
Usage:
UPDATE table
SET column = new value
WHERE column = value
Example: We want to change Andrew Fuller's title from "Vice President, Sales" to "CEO".
(Note: When using an update statement, it is very important to limit the
scope by using a where clause. If no where clause is used, all the records
will have the title CEO.)
(This is the employees table before we run the update statement.)
|
EmployeeID
|
LastName
|
FirstName
|
Title
|
BirthDate
|
HireDate
|
Address
|
City
|
|
1
|
Davolio
|
Nancy
|
Sales
Representative
|
12/8/1948
|
5/1/1992
|
507 - 20th
Ave. E.
|
Seattle
|
|
2
|
Fuller
|
Andrew
|
Vice
President, Sales
|
2/19/1952
|
8/14/1992
|
908 W.
Capital Way
|
Tacoma
|
|
3
|
Leverling
|
Janet
|
Sales
Representative
|
8/30/1963
|
4/1/1992
|
722 Moss
Bay Blvd.
|
Kirkland
|
|
4
|
Peacock
|
Margaret
|
Sales
Representative
|
9/19/1937
|
5/3/1993
|
4110 Old
Redmond Rd.
|
Redmond
|
|
5
|
Buchanan
|
Steven
|
Sales
Manager
|
3/4/1955
|
10/17/1993
|
14 Garrett
Hill
|
London
|
|
6
|
Suyama
|
Michael
|
Sales
Representative
|
7/2/1963
|
10/17/1993
|
123 Miner
Rd.
|
London
|
|
8
|
Callahan
|
Laura
|
Inside
Sales Coordinator
|
1/9/1958
|
3/5/1994
|
4726 -
11th Ave. N.E.
|
Seattle
|
|
9
|
Dodsworth
|
Anne
|
Sales
Representative
|
1/27/1966
|
11/15/1994
|
7
Houndstooth Rd.
|
London
|
|
10
|
Smith
|
John
|
Accountant
|
1/1/1970
|
2/2/2005
|
123 Main
St
|
Denver
|
Notice that in our update statement, in the where clause, we opted to use the unique field
EmployeeID. We did not want a field like FirstName in our where clause because
in a large company there may be multiple people with the same first name.)
(Now we will run our update statement below.)
UPDATE employees
SET title = 'CEO'
WHERE EmployeeID = 2
Results:
(1 row(s) affected)
(This is the employees table after we run the update statement. Note that EmployeeID 2
Andrew Fuller has the title of CEO.)
|
EmployeeID
|
LastName
|
FirstName
|
Title
|
BirthDate
|
HireDate
|
Address
|
City
|
|
1
|
Davolio
|
Nancy
|
Sales
Representative
|
12/8/1948
|
5/1/1992
|
507 - 20th
Ave. E.
|
Seattle
|
|
2
|
Fuller
|
Andrew
|
CEO
|
2/19/1952
|
8/14/1992
|
908 W.
Capital Way
|
Tacoma
|
|
3
|
Leverling
|
Janet
|
Sales
Representative
|
8/30/1963
|
4/1/1992
|
722 Moss
Bay Blvd.
|
Kirkland
|
|
4
|
Peacock
|
Margaret
|
Sales
Representative
|
9/19/1937
|
5/3/1993
|
4110 Old
Redmond Rd.
|
Redmond
|
|
5
|
Buchanan
|
Steven
|
Sales
Manager
|
3/4/1955
|
10/17/1993
|
14 Garrett
Hill
|
London
|
|
6
|
Suyama
|
Michael
|
Sales
Representative
|
7/2/1963
|
10/17/1993
|
123 Miner
Rd.
|
London
|
|
8
|
Callahan
|
Laura
|
Inside
Sales Coordinator
|
1/9/1958
|
3/5/1994
|
4726 -
11th Ave. N.E.
|
Seattle
|
|
9
|
Dodsworth
|
Anne
|
Sales
Representative
|
1/27/1966
|
11/15/1994
|
7
Houndstooth Rd.
|
London
|
|
10
|
Smith
|
John
|
Accountant
|
1/1/1970
|
2/2/2005
|
123 Main
St
|
Denver
|