3.9 Complex Delete
Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or
Already have an account? Sign In »
Video Transcription
00:00
Welcome back the Model Three sequel programming. This is lesson to queries. Sub lesson 2.5 The complex delete statement and the previous lesson we did a complex updates statement that had a relationship between three tables that controlled the selection of records of which fields to update.
00:19
Now we could do that same
00:21
type of update, or I mean, delete in a delete statement. However, to vary it a little bit, we're going to use a sub quarry. We're going to use a sub query that selects the company I. D. S that exists in the database and deletes any records from a specific table that don't have that company idea.
00:39
So to get started, we need to go to the e p A. Employees position Cavey extension table.
00:46
If you can filter out by writing it into the filter
00:51
and it's this table right here, So we're gonna give that a DoubleClick. We're gonna make sure we have the data tab of that table opened. We're gonna right click on an empty space
01:02
and click generate mock data.
01:04
The default, they're fine. 50.
01:07
You may have slightly more If you didn't change it the last time we were in this menu. But whatever size you want to use this fine
01:14
and I'm going to go ahead and click, Start gonna close it, I'm gonna refresh
01:19
There's my data. I'm gonna add a couple extra twos because I know too is going to exist. So I want a few more records than one or two.
01:26
I'm gonna save that.
01:29
And now I'm gonna start writing my queries. And a good way to start writing some queries when you're new to them is to write them as their own separate query in another tab.
01:38
So
01:40
the easiest courage for us to write will actually be the company table to query. So I'm going to go and filter out the company table.
01:48
That's this table. We're gonna want to select the company. I D. As this table is a record of companies that exist within the database. So let's start writing that it's gonna be select
02:00
company I d
02:04
from company,
02:08
where
02:12
actually, we don't need a square statement for this.
02:17
There's the companies. We get back one and two,
02:22
so that is actually going to be our sub quarry.
02:25
Let's go ahead and start writing Army inquiry, which will be the delete statement,
02:30
so we're going to start by saying Delete.
02:38
Okay, let's take a quick look.
02:40
So the syntax is the lead from table,
02:45
So delete from
02:47
E P employees
02:51
Baby extension
02:53
Where
02:54
again the least. It was a little simpler where it's tables. Conditions.
03:00
So we'll say Where company I d
03:04
not in.
03:08
We're going to drop this parentheses down here and then we're just going to Copy and paste are sub query into that.
03:16
So let's go ahead and walk through this query that we're writing. We're saying that we want to delete from the e p A Employees position extension table.
03:27
Any record where the company I d is not in
03:31
the long list. In the following list, we select the active company I d. From the company table.
03:38
So let's go ahead and run that I take one quick look at the table again.
03:44
We see all these records here.
03:46
We expect that these two and this one will stay around. So let's go ahead and run that Cory and see if that occurs.
04:00
They said it updated 45 rows, which sounds about right.
04:03
I'm gonna go ahead and refresh this data set
04:08
And sure enough, we kept around the ones and the twos
04:13
because that is what exists in the company table.
04:16
And we accomplished that using a delete statement that relied on a sub query
04:23
for targeting records in another table. So that completes this lesson on a complicated
04:30
delete statement. And for this, the least statement. We used a sub quarry. Now, these statements can get much more complicated, as you may have guessed, but we're gonna start simple or
04:43
restart. I'm sorry. We're gonna start simple, and then we're gonna move our way up and get Maura and more complicated with the commands were running against the database. So I hope you enjoy this lesson. Hope you got your delete statement working, and I will see you in the next lesson. Thank you.
Up Next
Instructed By
Similar Content
