Product <\/strong>table:\n<\/p>\nUSE AdventureWorks2014;\r\nGO\r\nSELECT *\r\nFROM Production.Product p;<\/pre>\n\n The query returns all records from the Product<\/strong> table of items that are sold or used in theMakeFlag<\/strong> that can be either 0<\/strong> or 1<\/strong> as shown below:\n<\/p>\n\n \n 0<\/strong> = Product is purchased\n<\/p>\n\n 1<\/strong> = Product is manufactured in-house\n<\/p>\nSimple UPDATE statement<\/h3>\n \n Let’s now run a simple UPDATE<\/strong> statement following the SQL UPDATE syntax described earlier:\n<\/p>\nUPDATE Production.Product\r\nSET \r\n Production.Product.MakeFlag = 1\r\nWHERE Production.Product.ProductID = 4;<\/pre>\n\n After the execution, 1 row affected message will be returned indicating that the statement went through:\n<\/p>\n
\n \n Before the query was executed, the Headset Ball Bearings<\/strong> product was purchased but now it\u2019s\n Update multiple rows<\/h3>\n \n Let’s move on and look at another example to see how we can update multiple rows at once. This time, we’ll also use
\n Use the query below to see what we have in the Employee<\/strong> table:\n<\/p>\nSELECT *\r\nFROM HumanResources.Employee e\r\nORDER BY e.VacationHours DESC;<\/pre>\n\n The Employee<\/strong> table has information such as salary, department, title, etc. but we’re interested in the number of available vacation hours:\n<\/p>\n\n \n We have a lot of different departments and job titles in the sample database, so let\u2019s run another query and filter
SELECT *\r\nFROM HumanResources.Employee e\r\nWHERE e.JobTitle = 'Quality Assurance Technician';<\/pre>\n\n Here we can see that we have four people with this job title and their available vacation hours:\n<\/p>\n
\n \n Let\u2019s just say that those guys have been performing really great for the past three months and we want to reward
UPDATE HumanResources.Employee\r\nSET \r\n HumanResources.Employee.VacationHours = HumanResources.Employee.VacationHours * 1.2\r\nWHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';<\/pre>\n\n We should see a message that 4 rows are affected meaning records for those four people from the QA department have been updated:\n<\/p>\n
\n \n We can check how this update reflected vacation hours by re-executing the quick-select from the Employee<\/strong> table. You\u2019ll notice that vacation hours are increased by 20 percent:\n<\/p>\n\n \n Now, there\u2019s another SQL UPDATE syntax that we can use to get the exact same result. Instead of saying column name =
UPDATE HumanResources.Employee\r\nSET \r\n HumanResources.Employee.VacationHours *= 1.2\r\nWHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';<\/pre>\n\n This is just another way to use the SET statement from the SQL UPDATE syntax and specify the list of columns or
\n It goes without saying that you can perform arithmetic operators like addition(+), subtraction(-), multiplication(*)
UPDATE HumanResources.Employee\r\nSET \r\n HumanResources.Employee.VacationHours \/= 1.2\r\nWHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';<\/pre>\nUpdate data using JOIN<\/h3>\n \n After going through some basics, let\u2019s see SQL UPDATE syntax on how to do updates based on joins. This can be a
\n This can be tricky at first compared to joining stuff in a SELECT statement and it’s not always straightforward, but
\n With that in mind, let\u2019s forget the existing data in our sample database for a moment and see how to do updatesExecute<\/strong>:\n<\/p>\nCREATE TABLE dbo.Bank\r\n(ID INT NOT NULL, \r\n BankName NVARCHAR(255) NULL, \r\n City NVARCHAR(255) NULL, \r\n SwiftCode NVARCHAR(255) NULL, \r\n CONSTRAINT PK_Bank PRIMARY KEY CLUSTERED(ID ASC)\r\n);\r\nGO\r\nCREATE TABLE dbo.SwiftCode\r\n(ID INT NOT NULL, \r\n BankID INT NOT NULL, \r\n Code NVARCHAR(11) NULL, \r\n CONSTRAINT PK_SwiftCode PRIMARY KEY CLUSTERED(ID ASC)\r\n);\r\nGO<\/pre>\n\n We just created two empty tables on the dbo<\/strong> schema; Bank<\/strong> and SwiftCode<\/strong>:\n<\/p>\n\n \n I entered just a few records in both tables and if we do a quick-select from both tables, here’s how it looks:\n<\/p>\n
\n \n As can be seen above, we\u2019re missing the SwiftCode<\/strong> information from the Bank<\/strong> table.SwiftCode<\/strong> table, we just need to join those two tables usingBankID<\/strong> and update the Bank<\/strong> table using the information from another table.\n<\/p>\n\n So, let\u2019s look at the SQL UPDATE syntax below for achieving this:\n<\/p>\n
UPDATE b\r\nSET \r\n b.SwiftCode = sc.Code\r\nFROM Bank b\r\n INNER JOIN SwiftCode sc ON sc.BankID = b.id;<\/pre>\n\n Once executed, you should see a message that 4 rows are affected by this action:\n<\/p>\n
\n \n This means that we just updated the table by using data from another table. Cool, right? If we query the Bank<\/strong> table one more time, here\u2019s what we should have:\n<\/p>\n\n \n When working on a more complex query, the rule of thumb is to always write a SELECT statement first and to just join
\n If you\u2019re new to SQL and don\u2019t quite understand what an alias is, by definition, aliases are temporary names toApexSQL Complete<\/a> which does this for me byConclusion<\/h2>\n \n The UPDATE statement is one of the three big statements of the DML side of the T-SQL language that deals with data
\n I hope this article has been informative for you and I thank you for reading it.\n<\/p>\n