{"id":525,"date":"2011-07-19T18:58:14","date_gmt":"2011-07-19T18:58:14","guid":{"rendered":"http:\/\/craftydba.com\/?p=525"},"modified":"2011-07-19T19:12:40","modified_gmt":"2011-07-19T19:12:40","slug":"updating-data","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=525","title":{"rendered":"Updating Data"},"content":{"rendered":"

The UPDATE<\/a> reservered word is part of the Data Manipulation Language (DML) defined by Codd<\/a> and is used to modify existing row data in one table in the database.<\/p>\n

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.<\/p>\n

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.<\/p>\n

-- MS sample database\r\nUSE [AdventureWorks2008R2];  GO\r\n\r\n-- Find employees with < 2 weeks vacation\r\nSELECT * FROM HumanResources.Employee AS E \r\nWHERE E.VacationHours <= 80;  GO       \r\n\r\n-- Give every employee matching this criteria an extra day\r\nUPDATE HumanResources.Employee\r\nSET VacationHours = VacationHours + 8.0\r\nWHERE VacationHours <= 80;  GO\r\n<\/span><\/pre>\n
 <\/p>\n
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.<\/p>\n
-- Find male employees who are single (109)\r\nSELECT * FROM HumanResources.Employee AS E \r\nWHERE LOWER(E.Gender) = 'm' AND UPPER(E.MaritalStatus) = 'S';  GO \r\n\r\n-- Reduce the sick and vacation time for these employees by 4 hours\r\nUPDATE HumanResources.Employee\r\nSET VacationHours = VacationHours - 4.0,\r\n  SickLeaveHours = SickLeaveHours - 4.0\r\nWHERE LOWER(Gender) = 'm' AND UPPER(MaritalStatus) = 'S';  GO\r\n<\/span><\/pre>\n
 <\/p>\n
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.  <\/p>\n
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.<\/p>\n
Please see both the ANSI and TSQL versions of the UPDATE statement below.<\/p>\n
-- Find all Sales people in the United States (12)\r\nSELECT * \r\nFROM\r\n  HumanResources.Employee AS e \r\n  INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID \r\n  INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID \r\n  INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID \r\nWHERE \r\n   E.JobTitle LIKE '%SALES%' AND UPPER(sp.CountryRegionCode) = 'US';  GO\r\n      \r\n-- ANSI Standard Syntax + 40 hrs vacation\r\nUPDATE HumanResources.Employee \r\nSET VacationHours = VacationHours + 40.0\r\nWHERE EXISTS \r\n( \r\nSELECT bea.BusinessEntityID \r\nFROM\r\n    Person.BusinessEntityAddress AS bea \r\n    INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID \r\n    INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID \r\nWHERE \r\n   bea.BusinessEntityID = HumanResources.Employee.BusinessEntityID AND \r\n   HumanResources.Employee.JobTitle LIKE '%SALES%' \r\n   AND UPPER(sp.CountryRegionCode) = 'US'\r\n);  GO\r\n\r\n-- TSQL Enhanced Syntax + 40 hrs vacation\r\nUPDATE HumanResources.Employee \r\nSET VacationHours = VacationHours + 40.0\r\nFROM\r\n  HumanResources.Employee AS e\r\n  INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID \r\n  INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID \r\n  INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID \r\nWHERE \r\n   E.JobTitle LIKE '%SALES%' AND UPPER(sp.CountryRegionCode) = 'US';  GO\r\n<\/span><\/pre>\n
 <\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[31,12,15,28,29,32],"class_list":["post-525","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-database-developer","tag-free-code","tag-john-f-miner-iii","tag-sql-server","tag-tsql","tag-update"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/525","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=525"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/525\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=525"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=525"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=525"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}