Professional SQL Server™ 2005 Programming

3.4. The DELETE Statement

The version of the DELETE statement that we'll cover in this chapter may be one of the easiest statements of them all. There's no column list — just a table name and, usually, a WHERE clause. The syntax couldn't be much easier:

DELETE [TOP (<expression>) [PERCENT]
[FROM ] <table_name>
[FROM ] <table list/JOIN conditions>
[WHERE <search condition>]

The tricky thing on this is the two FROM clauses (nope, that is not a typo). You can think of this as being somewhat like the UPDATE statement in the sense that you need to say what table you actually want to delete from, and the second is a more genuine FROM clause including JOINs if you so choose (ostensibly to help you to determine what rows you want to delete).

The WHERE clause works just like all of the WHERE clauses we've seen thus far. We don't need to provide a column list because we are deleting the entire row (you can't delete half a row for example).

Since the first form of DELETE is so easy, we'll perform only a quick example. Let's utilize the sample we built for joins early in the chapter. In case you skipped that, here is the code that built those tables.

If you built the FULL JOIN sample from earlier in the chapter, you can just ignore this build script, as they are identical.

CREATE TABLE Film (FilmID int PRIMARY KEY, FilmName varchar(20) NOT NULL, YearMade smallint NOT NULL ); CREATE TABLE Actors (FilmID int NOT NULL, FirstName varchar(15) NOT NULL, LastName varchar(15) NOT NULL, CONSTRAINT ...