SQL Update Statement

The update statement is a really useful one. It lets you edit the value of a column, or a certain number of columns.

Or maybe everything changed and you have to alter every row in your table and start a fresh new life.
Starting from simple examples, we will see how to change your values in more fashonable ways, like how to update using values from another table (sql from select) or unleash your SQL power dealing with identity columns.

Contents for SQL Update Statement

1 SQL Update Syntax
2 Update from another table
3 Identity Column
4 REPLACE function

SQL Update Syntax

The structure of the update command is quite simple:

UPDATE [TableName]
SET [Column_Name_1] = NewValue
WHERE [Column_Name_2] = Comparison_Value;

[TableName]: The table you are dealing with.
[Column_Name_1] = The column whom value you wish to change
NewValue: The new value of Column_Name_1 for a certain set of rows.
WHERE [Column_Name_2] = Comparison_Value: Something like “Only the rows that have the Column_Name_2 value equals to Comparison_Value are to be changed”.
Keep in mind that Column_Name_1 and Column_Name_2 could be the same column, and the WHERE clause is optional.

Update from another table

Suppose you have a database structure like this:

And suddendly a war starts in Lapland (the reindeers can be violent). The first thing to do is to kidnap Santa Cl… To arm all your robots in the area.

It’s really simple.

UPDATE [RobotDog]
SET [Armed] = 1
WHERE [RobotFactoryId] =
(
SELECT [RobotFactoryId]
FROM [RobotFactory]
WHERE [Location] LIKE "Lapland"
);

Or suppose that you want to change the name of your robots to the name of the secret base in the North Pole:

UPDATE [RobotDog]
SET [Name] = (
SELECT TOP 1 [Name]
FROM [RobotFactory]
WHERE [Location] LIKE "North Pole"
);

Identity Column

Ideally, this is something you should not do, but sometimes it can really save you.

I remember, when I first started working in a new company, deleting and then inserting again a row. The problem was that in the application it was used the Id of that row, and the row inserted had a different Id. At the same time I could not change the value of the Id column, because it was an Identity Column.

Cannot update identity column 'ID'

The solution I found was to delete the newly inserted row and insert it back again, but with the wanted Id.

SET IDENTITY_INSERT [RobotFactory] ON
GO

DELETE FROM [RobotFactory] WHERE RobotFactoryId = [RowId_To_Be_Deleted]
GO

INSERT INTO [RobotFactory] (RobotFactoryId, Name, Location) VALUES ([RowId_Needed], [Old_Factory_Name], [Old_Factory_Location])
GO

SET IDENTITY_INSERT [RobotFactory] OFF

In this way you made up for your mistake.

REPLACE function

Suppose that you shift your business in Spain from robot dogs to robot pterodactyls. (It would be awesome)
It would be really helpful a function like the REPLACE one, which replaces a text inside a string with another text.

UPDATE [RobotDog]
SET [Name] = REPLACE([Name], 'Robot', 'Pterodactyl')
WHERE [RobotDogId] IN (
SELECT d.RobotDogId
FROM RobotDog d
INNER JOIN [RobotFactory] f ON f.RobotFactoryId = d.RobotFactoryId
WHERE f.Location LIKE 'Spain'
);