Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome back to Model three sequel programming. This is less than one sequel statements. And now we're on the final sub lesson of that lesson. 1.6 the delete statement. That's probably the statement that makes the most people nervous.
00:14
But let's go ahead and get started with it. So we're gonna go. We're going to again interact with the E p A employees position, Cavey extension table, the same table we used in the previous lesson
00:24
and to start off with I haven't empty. It's okay. If you still have a record in there, it won't make a difference. We're going to go ahead and use another neat feature of de Beber and generate some mock data for this table.
00:40
So if you right click on the data area,
00:46
you'll get an option to generate mock data. We're gonna go ahead and click on that
00:50
and you can I have the row counting the bass eyes set to 50. You consented to anything you like, though of course, the larger you said it, the longer it will take to complete. But I think 50 will be just fine for demonstrating the delete statement. So I'm gonna go ahead and click Start
01:06
and it is done. I'm gonna close this dialogue. I'm gonna click Refresh
01:11
And here we go. A bunch of mark data has been inserted into the table,
01:17
so let's go ahead and right Click the table and click
01:22
generate sequel
01:23
Delete
01:25
And again we will it
01:27
de Beber will show us the statement that will delete a record from the database. So let's go ahead and copy that
01:40
and let's see what our options are. We have a company.
01:49
Let's do 10 10 and our That's an easy one to remember, because we'll need to provide.
01:55
We don't have to provide all three keys, but typically when you're running into the lead statement an update statement, you want to be sure to, ah, use all the required keys. You don't have to. It just
02:06
it will help make sure that you don't accidentally ah, damage a relationship. If you view the diagram, we can see that those three are keys.
02:14
So
02:15
let's open a sequel. Editor New sequel editor.
02:19
Let's go ahead and haste in that command
02:22
again. It gave me the full name. I don't need the full name, so I'll just go ahead and get rid of that. It would work either way, though,
02:29
so we're record I d
02:31
or we'll start with company I d equals 10. We saw that was in there.
02:37
Field name was Let's go back and take a look.
02:42
Record idea and company I d was 10 field name was are all right?
02:49
So 10 10
02:52
So we're deleting from table where condition is true. So this the delete statements actually a little bit simpler. It's
03:01
table
03:02
condition. So delete from table where condition is true
03:07
Normally arranged this like this And when we run this we will get rid of this record.
03:15
So let's go ahead and run it.
03:21
We did something wrong.
03:23
We, uh, got an updated role of zero. We should have gotten a one.
03:29
Let's see what happened.
03:31
Record ideas. 10
03:35
Good ideas. 10.
03:37
Feel name is R
03:40
is there?
03:42
Ah, here's the problem. There's this face before that are
03:46
so it's not a match.
03:47
But if we do this,
03:50
we should get a match.
03:52
And we did
03:53
so when dealing with string of text, you do need to be
03:57
aware of such things that space is a value and it will cause
04:02
feels to not match if there's extra space where you don't expect it. So we got rid of that record,
04:10
and if we refresh, we will see it go away.
04:13
And sure enough, it went away.
04:15
Now let's say we wanted to clean out the table. How would we do that? Well, that's very easy. In fact,
04:23
we're going to say
04:26
delete from table
04:30
with no conditions
04:32
and we'll get a warning
04:34
because Dee Beavers knows this is clearing a table,
04:39
but we want to clear the table. So we're gonna go ahead and click, okay?
04:43
And we see that we
04:45
got rid of 49
04:46
records, which makes sense. We added 50. I deleted one. Before that left 49 on the table. We just deleted all of them.
04:53
Now we go back to the table and click. Refresh.
04:56
It's all gone now. Of course, if you were going to do that in a
05:00
production database, you would, of course want to be sure to back up that table first
05:06
and double check that that is the desired outcome. And, you know, sometimes it is in any case that finishes the delete statement. I hope you got your delete statement working. And, uh,
05:19
that finishes the lesson one of module three and I will see you in the next lesson. Thank you. Bye.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they 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