Deleting and Updating Records in SQL
Part two of a three-part series of blogs

This blog teaches you how to modify existing data in your database by either deleting records or updating them.

  1. Modifying Data in SQL
  2. Deleting Data From a Table (this blog)
  3. Updating Existing Data

This blog is part of our complete SQL tutorial. You can learn more about these techniques on our Advanced SQL training course.

Posted by Andrew Gould on 08 May 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Deleting Data From a Table

To delete data from a table you use the DELETE statement. This statement affects entire records, so you can't use this technique to remove a value from an individual column.

Deleting Records from a Table

The syntax for deleting records from a table is straightforward enough:

DELETE FROM MyTableName

WHERE Condition = True

So, for example, the code below will delete any film whose running time is less than 100 minutes:

DELETE FROM tblFilm

WHERE FilmRunTimeMinutes < 100

When you execute the code you'll be told how many records you have irretrievably removed from the table:

Result of delete

I hope you selected the correct records, because you can't get them back!

 

You can add multiple criteria to the WHERE clause when you're deleting records. You might want to read our blog on using criteria in queries to give you an idea of the things you can do.

Deleting Records from Different Tables

You might find that you need to delete records from one table using criteria that are based on values held in other tables. There are two solutions to this problem. The first technique involves the use of subqueries. The example below will delete any film from the film table that was directed by Steven Spielberg:

DELETE FROM tblFilm

WHERE FilmDirectorID =

(SELECT DirectorID

 FROM tblDirector

 WHERE DirectorName = 'Steven Spielberg')

The subquery in the above example returns the id number of Steven Spielberg. The WHERE clause compares this result with the value of the FilmDirectorID field to determine whether a row should be deleted.

You can achieve the same result by using a second FROM clause in the DELETE statement. The example below will again delete any films by Steven Spielberg:

DELETE FROM tblFilm

FROM

tblFilm AS f INNER JOIN

tblDirector AS d ON f.FilmDirectorID = d.DirectorID

WHERE

d.DirectorName = 'Steven Spielberg'

The second FROM clause specifies the set of records from which rows can be deleted. The first FROM clause deletes the corresponding rows from the specified table.

Deleting Every Record from a Table

There are two techniques you can use to delete every record from a table. The first method involves using the DELETE statement without a WHERE clause, as in the example below:

DELETE FROM tblFilm

It's more efficient however to use the TRUNCATE statement instead. This method is faster,  and uses fewer system resources and transaction log resources.

TRUNCATE TABLE tblFilm

What's Next?

We've seen how to remove data from a table but what if you only want to modify existing data? The answer is to use the UPDATE statement and the next part of this series explains how it works.

  1. Modifying Data in SQL
  2. Deleting Data From a Table (this blog)
  3. Updating Existing Data
This blog has 0 threads Add post