Preview
Video Player
00:00
00:00
Removing Specific Rows
3:17Like with SELECT and UPDATE statements, you can use a WHERE clause to narrow in on specific rows to remove from a table.
Deleting all rows in a table is one thing.
0:00
But how about the more useful
practice of targeting specific rows?
0:03
Let's start with the original
delete statement that deletes
0:08
all rows in a table.
0:11
How do you think we could select
the rows we wanted to delete?
0:14
Just like a select and update statement
when you use a WHERE condition,
0:18
you can use the same WHERE
conditions on DELETE statements too.
0:22
Conditions come in all shapes and sizes.
0:27
Remember, some have operators such as
equality, inequality, greater than, or
0:29
less than.
0:33
There's the conditions with the IN keyword
for searching within a set of values.
0:34
Our ranges would use the BETWEEN keyword.
0:39
And finally, there's the LIKE keyword for
patterns of characters.
0:42
Great news!
0:46
Our library has been saved.
0:46
It's no longer closing down.
0:48
We've restored the database
from our backup and here it is.
0:49
Unfortunately, we were only
able to buy back the books,
0:53
apart from the Harry Potter books.
0:56
But they're still here in the database.
0:58
Here's the books table and its contents.
1:02
How would we write the query to
retrieve all the Harry Potter books?
1:05
We could use the LIKE keyword.
1:09
Followed by, Harry Potter.
1:19
With a wildcard.
1:25
When we run the query,
all the Harry Potter books are returned.
1:27
Let's update this query
to a DELETE statement.
1:31
This statement will remove
all Harry Potter books.
1:43
Let's run it.
1:46
When we look at all the books now,
all of the Harry Potter books are gone.
1:48
Finally, let's do another DELETE example.
1:54
Michael, our patron with the id of 4,
has moved out of the area,
2:00
and no longer requires a library account.
2:05
Assuming they've returned
all of their books,
2:08
our policy is to delete
the information from our database.
2:11
We can do DELETE FROM
patrons WHERE id = 4.
2:16
Now they've been removed.
2:27
We can delete all the loan history
2:31
too by writing DELETE FROM loans WHERE
2:37
patron_id = 4; Now there's
2:43
no trace of Michael in our database.
2:48
Depending on your circumstances,
2:56
you can delete multiple rows based
on any condition you construct.
2:58
Remember, the WHERE conditions are exactly
the same as how you'd use them
3:02
in a SELECT statement.
3:06
Instead of retrieving the rows,
you're deleting them.
3:08
The change is permanent, so
3:11
be sure you're deleting exactly
what you want to be removed.
3:13