The UPDATE reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to modify existing row data in one table in the database.
Today, I would like to review the different ways to update data using the Adventure Works Sample database supplied by Microsoft. Each example has a SELECT statement to identify the number of rows affected before executing the UPDATE.
1 – The simplest form of the UPDATE statement modifies one column in a given table filtered by one where clause. The following example updates the employee table adding 8 hours to the VacationHours column for employee who has less than or equal to 80 hours remaining.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<span style="color: #008000;">-- MS sample database USE [AdventureWorks2008R2]; GO -- Find employees with < 2 weeks vacation SELECT * FROM HumanResources.Employee AS E WHERE E.VacationHours <= 80; GO -- Give every employee matching this criteria an extra day UPDATE HumanResources.Employee SET VacationHours = VacationHours + 8.0 WHERE VacationHours <= 80; GO </span> |
2 – A more complex form of the UPDATE statement modifies several columns filtered by several where clauses. The following example finds all single male employees in the Adventure Works company. Each employee has their Vacation and Sick Leave hours reduced by 4 to save the company money during these economic hard times.
|
1 2 3 4 5 6 7 8 9 10 |
<span style="color: #008000;">-- Find male employees who are single (109) SELECT * FROM HumanResources.Employee AS E WHERE LOWER(E.Gender) = 'm' AND UPPER(E.MaritalStatus) = 'S'; GO -- Reduce the sick and vacation time for these employees by 4 hours UPDATE HumanResources.Employee SET VacationHours = VacationHours - 4.0, SickLeaveHours = SickLeaveHours - 4.0 WHERE LOWER(Gender) = 'm' AND UPPER(MaritalStatus) = 'S'; GO </span> |
3 – The most complex form of the UPDATE statement updates one or more columns using several join tables and where clauses to get the resulting set. Transaction SQL supports an non-standard FROM key word that makes the query simpler; However, this form is not ANSI compitable.
If you are writing code that might have to be executed on another database platform in the future, do not use this form. The ANSI Standard syntax can be used with a EXISTS sub-query to achieve the same results.
Please see both the ANSI and TSQL versions of the UPDATE statement below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<span style="color: #008000;">-- Find all Sales people in the United States (12) SELECT * FROM HumanResources.Employee AS e INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID WHERE E.JobTitle LIKE '%SALES%' AND UPPER(sp.CountryRegionCode) = 'US'; GO -- ANSI Standard Syntax + 40 hrs vacation UPDATE HumanResources.Employee SET VacationHours = VacationHours + 40.0 WHERE EXISTS ( SELECT bea.BusinessEntityID FROM Person.BusinessEntityAddress AS bea INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID WHERE bea.BusinessEntityID = HumanResources.Employee.BusinessEntityID AND HumanResources.Employee.JobTitle LIKE '%SALES%' AND UPPER(sp.CountryRegionCode) = 'US' ); GO -- TSQL Enhanced Syntax + 40 hrs vacation UPDATE HumanResources.Employee SET VacationHours = VacationHours + 40.0 FROM HumanResources.Employee AS e INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID WHERE E.JobTitle LIKE '%SALES%' AND UPPER(sp.CountryRegionCode) = 'US'; GO </span> |
In summary, working with databases is vital in the information age. That said, modifying data is a daily task that developers and administrators perform. Knowing the different variations of the UPDATE statement will help you select the correct syntax.
I was reading through some of your content on this internet site and I conceive this site is real instructive! Retain putting up.