This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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.

Parts of this blog
Modifying Data in SQL Deleting Data From a Table (this blog) Updating Existing Data

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Deleting Data From a Table

Deleting Records from a Table

Deleting Records from Different Tables

Deleting Every Record from a Table

What's Next?

Head office

London

Manchester