560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of our complete SQL tutorial. You can learn more about these techniques on our Advanced SQL training course. |
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.
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.
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.
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
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.