DEV Community

Cover image for Learn SQL: Microsoft SQL Server - Episode 14: Update records in a table
Goran Kortjie
Goran Kortjie

Posted on

Learn SQL: Microsoft SQL Server - Episode 14: Update records in a table

goran-greeting

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.

select-human-resource-department-table

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]
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

When we execute this Update Statement and then check the data in the HumanResourcesDepartment table, we should see the data has updated.

update-table

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]
Enter fullscreen mode Exit fullscreen mode

By using this Select Statement before we perform our Update Statement we can confirm that we are working with the correct data.

check-before-update

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]
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

update-multiple-columns

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.

baby-yoda

Discussion (0)