Have you ever wondered how to update your data in a table by using SQL Statements? In this entry we are going to explore and demonstrate how we can accomplish this exciting task.
The task of updating data comes in handy, when we find the data in the table to be incorrect, or in a scenario where we need to make some changes based on some new information we received.
To demonstrate this, let’s use a SELECT statement on the HumanResourcesDepartment table in SSMS.
The result set of running this SQL statement gives us the data currently inside the HumanResourcesDepartment table.
I have inserted the data from the previous episode, back into the table for consistency.
Imagine we made a mistake with the last entry when we inserted the data into the table and instead of 'Growth and Education', the GroupName column should contain 'Growth and Development'. To accomplish this, we need to construct an Update Query Statement.
The syntax for the update statement is as follows:
update [Table-Name]
set [Column-Name] = value
Where [Unique-Identifier] = [Identifier]
Ensure the unique identifier is in fact unique in the table, usually the key for the table would be unique and therefore makes an excellent value to use.
By following this syntax, if we want to change the value in GroupName, this is how it would look.
update [HumanResources].[Department]
set GroupName = 'Growth and Development'
Where DepartmentID = 17
When we execute this Update Statement and then check the data in the HumanResourcesDepartment table, we should see the data has updated.
Just to make it absolutely clear, the Where clause inside of your Update Statement is very scary, if we provide it with incorrect information it could end up updating data where we did not intend or worse update an entire column of data.
I personally would double-check the column I need to update by using a Select statement.
Select * from [Table-Name]
Where [Unique-Identifier] = [Identifier]
By using this Select Statement before we perform our Update Statement we can confirm that we are working with the correct data.
Update Multiple Columns
We can even update multiple columns in one SQL statement. For instance, we want to change the value of the Name column from 'Learning' to 'Upskill'
All we need to do is separate the Column-Name in the SET Clause with dashes.
update [Table-Name]
set [Column-Name] = value, [Column-Name] = value, ...
Where [Unique-Identifier] = [Identifier]
We can update as many columns as we want for the given table.
Let’s update the Name and ModifiedDate for the row with the DepartmentID = 17.
update [HumanResources].[Department]
set GroupName = 'Growth and Development', Name = 'Upskill', ModifiedDate = getDate()
Where DepartmentID = 17
That's all, this is how we can update records in a table. Easy right!
I really hope this entry was informative and helps you along your journey to mastering SQL.
Top comments (0)