Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

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

Introduction to SQL

This introductory SQL training course teaches SQL core concepts that are applied in professional environments. Once completed with the course, students will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor