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.
Now we could do that same
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.
So to get started, we need to go to the e p A. Employees position Cavey extension table.
If you can filter out by writing it into the filter
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
and click generate mock data.
The default, they're fine. 50.
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
and I'm going to go ahead and click, Start gonna close it, I'm gonna refresh
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.
I'm gonna save that.
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.
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.
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
actually, we don't need a square statement for this.
There's the companies. We get back one and two,
so that is actually going to be our sub quarry.
Let's go ahead and start writing Army inquiry, which will be the delete statement,
so we're going to start by saying Delete.
Okay, let's take a quick look.
So the syntax is the lead from table,
again the least. It was a little simpler where it's tables. Conditions.
So we'll say Where company I d
We're going to drop this parentheses down here and then we're just going to Copy and paste are sub query into that.
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.
Any record where the company I d is not in
the long list. In the following list, we select the active company I d. From the company table.
So let's go ahead and run that I take one quick look at the table again.
We see all these records here.
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.
They said it updated 45 rows, which sounds about right.
I'm gonna go ahead and refresh this data set
And sure enough, we kept around the ones and the twos
because that is what exists in the company table.
And we accomplished that using a delete statement that relied on a sub query
for targeting records in another table. So that completes this lesson on a complicated
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
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.