Basic DELETE statement
Published: 3rd May 2013
Okay, this time an UPDATE simply won’t suffice. Something is badly wrong and we need to remove a record rather than just update it.

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


What we’ve decided is that Brian is, in fact, a dog. Now we probably should have seen this earlier but by then it was too late… he was in the table.

So how can we get around this? Maybe drop the table entirely, re-create it, and re-enter the data without Brian? Well that’s one option, although it would be madness really. Imagine a table with 100 million rows…

Therefore the only option is to remove the record. To do this we use DELETE.

DELETE is quite similar to UPDATE with regard to syntax. Therefore, if you have read my previous articles, you’ll recognise the syntax a little:

delete from dbo.person
where firstName = 'Brian'
and lastName = 'Griffin'


Run that and that’s it… the record has been dropped. SQL Server has no “Are you sure?”, therefore it’s best to check your logic beforehand with a select statement. In this case I’ll delete all records in which the person is not a parent…

select *
--delete
from dbo.person
where isParent = 0


Therefore we can simply comment out the SELECT and uncomment the DELETE:

--select *
delete
from
dbo.person
where isParent = 0


And that’s it… our table now only includes parents:

Also note that to empty a table completely you can type the following:

delete
from
dbo.person


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