BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog teaches you how to modify existing data in your database by either deleting records or updating them.
- Modifying Data in SQL
- Deleting Data From a Table (this blog)
- 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:
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.
- Modifying Data in SQL
- Deleting Data From a Table (this blog)
- Updating Existing Data