Deleting Table Rows Using DELETE

Introduction

The DELETE statement in SQL allows you to remove rows from a table. Whether you are cleaning up data or enforcing business rules, learning how to use this statement effectively is crucial. In this guide, you will learn how to:

Write DELETE statements to remove specific rows.
Use the WHERE clause to target rows for deletion.
Avoid common pitfalls when using DELETE.

The DELETE Statement

The basic syntax of the DELETE statement is:

DELETE FROM table_name
WHERE condition;

Key points to remember:

The WHERE clause specifies which rows to delete. Without it, all rows in the table will be deleted.
Deleting all rows with DELETE is different from dropping the table. The table structure remains intact, but it will be empty.
Always double-check your WHERE clause to avoid accidental data loss, especially in production environments.

Example Table

Let’s revisit the puppies table for practice:

name	age_yrs	breed	weight_lbs	microchipped
Cooper	1	Miniature Schnauzer	18	1
Indie	0.5	Yorkshire Terrier	13	1
Kota	0.7	Australian Shepherd	26	0
Zoe	0.8	Korean Jindo	32	1
Charley	1.5	Basset Hound	25	0
Ladybird	0.6	Labradoodle	20	1
Callie	0.9	Corgi	16	0
Jaxson	0.4	Beagle	19	1
Leinni	1	Miniature Schnauzer	25	1
Max	1.6	German Shepherd	65	0

Deleting Specific Rows

To delete rows based on a condition, use the WHERE clause. For example, to remove all puppies that are not microchipped (microchipped = 0):

DELETE FROM puppies
WHERE microchipped = 0;

After running this statement, verify the results using:

SELECT * FROM puppies;

The remaining rows will be:

name	age_yrs	breed	weight_lbs	microchipped
Cooper	1	Miniature Schnauzer	18	1
Indie	0.5	Yorkshire Terrier	13	1
Zoe	0.8	Korean Jindo	32	1
Ladybird	0.6	Labradoodle	20	1
Jaxson	0.4	Beagle	19	1
Leinni	1	Miniature Schnauzer	25	1

Understanding the WHERE Clause

The WHERE clause in a DELETE statement functions similarly to its use in SELECT statements. It identifies rows based on conditions such as:

Equality: WHERE column = value
Greater/Less Than: WHERE column > value
Text Matching: WHERE column LIKE 'pattern'

Example: To delete puppies weighing more than 30 lbs:

DELETE FROM puppies
WHERE weight_lbs > 30;

Precautions When Using DELETE

The DELETE statement is powerful but potentially destructive. Here are best practices to follow:

Always include a WHERE clause unless you intentionally want to delete all rows.
Double-check your conditions before executing the query.
Back up your data before running DELETE statements in a production environment.
Use SELECT with the same WHERE clause first to preview the rows that will be deleted.

What You Learned

How to use the DELETE statement to remove rows from a table.
How to target specific rows for deletion using the WHERE clause.
Precautions and best practices to avoid accidental data loss.

Mastering the DELETE statement allows you to maintain and clean your database efficiently. Use it carefully and wisely to ensure data integrity.