Basic UPDATE statement
Published: 3rd May 2013
Okay, we’ve inserted data, selected data, but what happens if we find some of our data is wrong? That’s where we need to UPDATE our records.

As in previous posts, if you have not read the Basic INSERT Statement document then please run the following code as we will use the resulting table in our examples:

if OBJECT_ID('dbo.person') is not null drop table dbo.person
go

create table dbo.person
(
  
id smallint identity(1, 1) not null,
  
title varchar(4) not null,
  
firstName varchar(20) not null,
  
lastName varchar(30) not null,
  
hairColour varchar(10) null,
  
isParent bit not null,
  
dateCreated datetime not null default(current_timestamp),
  
modifiedDate datetime null,
  
constraint pk_person primary key (id)
)
insert into dbo.person
select 'Mr', 'Bart', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP, null union all
select 'Ms', 'Lisa', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP, null union all
select 'Mrs', 'Marge', 'Simpson', 'Blue', 1, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Homer', 'Simpson', null, 1, CURRENT_TIMESTAMP, null union all
select 'Ms', 'Maggie', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Peter', 'Griffin', 'Brown', 1, CURRENT_TIMESTAMP, null union all
select 'Mrs', 'Lois', 'Griffin', 'Red', 1, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Chris', 'Griffin', 'Blonde', 0, CURRENT_TIMESTAMP, null union all
select 'Ms', 'Meg', 'Griffin', 'Brown', 0, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Stewie', 'Griffin', null, 0, CURRENT_TIMESTAMP, null union all
select 'Mr', 'Brian', 'Griffin', 'White', 0, CURRENT_TIMESTAMP, null


Okay, so let’s have a quick look at our table…

select *
from dbo.person


A few people could easily dispute the fact that this table implies Homer is bald. Therefore we now need to update the table to set that record straight (as he does have a couple of strands).

To do this we’ll presume his few strands are black and update the table as follows:

update dbo.person
set hairColour = 'Black'
where firstName = 'Homer'
and lastName = 'Simpson'


We could also refer to him by his ID (in this case 4), but I chose to use his full name.

Looking at the table shows us that the update has worked, but it also highlights a problem:

As you can see, Homer now has black hair. However, what about the modified date? It’s still blank.

We don’t want to have to update everything twice (once for the data field and once more for the modified date), so what we do is simply change the update statement to affect more than one column. This is simple enough:

update dbo.person
set hairColour = 'Black',
  
modifiedDate = CURRENT_TIMESTAMP
where firstName = 'Homer'
and lastName = 'Simpson'


Now we have the following:

This is much better.

With an update statement always make sure that your where clause is precise enough so that you don’t update more records than you expect. SQL Server doesn’t have an “undo” option!

A good way to test this is to create a SELECT statement first. Let’s change Meg’s name to “Megan”:

select firstName
from person
where firstName = 'Meg'
and lastName = 'Griffin'


Now we know that we have the correct piece of data selected we can simply transform this statement into an UPDATE. To do this simply remove the SELECT… FROM… and replace with UPDATE… SET. I tend to leave the select in place, but simply comment it out…

--select firstName
--from person
update dbo.person
set firstName = 'Megan', modifiedDate = CURRENT_TIMESTAMP
where firstName = 'Meg'
and lastName = 'Griffin'


The table updates as required…

Comments:
NB: Comments will only appear once they have been moderated.