1
00:00:00,120 --> 00:00:00,600
Okay.

2
00:00:00,870 --> 00:00:05,310
So we've made it through the C, r and you portions of crud.

3
00:00:05,910 --> 00:00:11,100
Now we finally made it to DX, which stands for Delete or Destroy, depending on who you ask.

4
00:00:11,280 --> 00:00:17,940
So in this video we're going to see how to delete things in my SQL, and it's pretty straightforward.

5
00:00:17,940 --> 00:00:23,790
It's very similar to selecting things, except rather than just selecting something, you're going to

6
00:00:23,790 --> 00:00:24,270
delete it.

7
00:00:24,270 --> 00:00:25,800
So let me show you what I mean.

8
00:00:27,030 --> 00:00:32,250
Here's an example that will delete any cat with the name egg in our database.

9
00:00:32,850 --> 00:00:36,330
So imagine first of all that we were selecting.

10
00:00:36,570 --> 00:00:40,860
All we would have is select from cats where name is equal to egg.

11
00:00:41,280 --> 00:00:48,750
So if we ran that it would give us the cat or cats with name egg and we can swap out select with delete

12
00:00:48,750 --> 00:00:49,890
and that will delete it.

13
00:00:49,890 --> 00:00:55,710
So that same rule of thumb from before where I said make sure before you update something that your

14
00:00:55,710 --> 00:01:00,570
selection is correct, then do the update because it will mean there's no undo button.

15
00:01:00,570 --> 00:01:06,360
Unfortunately, same holds true and more so with delete because your data is gone as soon as you delete

16
00:01:06,360 --> 00:01:06,720
it.

17
00:01:07,080 --> 00:01:08,190
So let's try this.

18
00:01:08,190 --> 00:01:09,420
We'll delete eg.

19
00:01:10,260 --> 00:01:13,500
Let's do first of all select star from cats.

20
00:01:13,500 --> 00:01:15,390
Just make sure we have egg in here.

21
00:01:15,930 --> 00:01:17,640
There he or she is.

22
00:01:17,640 --> 00:01:21,870
I'm not quite sure what I was intending with that name.

23
00:01:21,870 --> 00:01:27,330
EG So what we'll do first is just make sure we can select appropriately before we delete.

24
00:01:27,630 --> 00:01:34,530
So select star from cats where name equals egg.

25
00:01:34,860 --> 00:01:39,030
Remember that this is case and sensitive just for now.

26
00:01:39,210 --> 00:01:41,220
I'll do lowercase just to prove that.

27
00:01:41,220 --> 00:01:42,450
All right, so we get egg.

28
00:01:42,450 --> 00:01:43,140
Perfect.

29
00:01:43,410 --> 00:01:49,890
So now to remove egg delete from and then we can just copy this.

30
00:01:52,190 --> 00:01:56,870
So before I hit enter, I do need to take a moment to address something pretty serious.

31
00:01:57,710 --> 00:02:00,760
And that is that no cats are being harmed.

32
00:02:00,770 --> 00:02:02,330
No cats are dying here.

33
00:02:02,420 --> 00:02:04,460
Yes, they're being deleted from this table.

34
00:02:04,460 --> 00:02:07,970
But in reality, deletion doesn't mean death.

35
00:02:08,270 --> 00:02:14,630
It means that they're being adopted, perhaps to a loving family that will treat them with the care

36
00:02:14,630 --> 00:02:15,730
that they deserve.

37
00:02:15,740 --> 00:02:21,380
So there's no death here, and I hope I don't receive any furious comments about how I'm treating animals

38
00:02:21,380 --> 00:02:21,860
in my course.

39
00:02:21,860 --> 00:02:22,950
I love animals.

40
00:02:22,970 --> 00:02:26,000
I learned from my first course I made a mistake.

41
00:02:26,000 --> 00:02:31,130
I will never refer to deleting dogs or cats as killing them.

42
00:02:31,700 --> 00:02:33,050
I'm very sorry for that.

43
00:02:33,650 --> 00:02:35,780
So again, these cats are not dying.

44
00:02:35,900 --> 00:02:38,720
So with that out of the way, let's hit enter.

45
00:02:41,680 --> 00:02:46,870
Now let's go back and select star from Cats.

46
00:02:48,250 --> 00:02:51,310
And as you can see, Egg is now gone.

47
00:02:52,120 --> 00:02:54,640
Also something that does come up sometimes.

48
00:02:54,670 --> 00:02:57,860
Notice that the cat IDs have not shifted.

49
00:02:57,880 --> 00:02:59,040
They're still the same.

50
00:02:59,050 --> 00:03:04,600
Some of my students in the past have had the expectation that the IDs would alter, but they always

51
00:03:04,600 --> 00:03:05,680
will stay the same.

52
00:03:05,680 --> 00:03:11,860
And that's because when something is inserted into a table that unique ID is generated for it.

53
00:03:11,860 --> 00:03:18,040
Remember, it's a primary key, and eventually we'll learn about having multiple tables that interact

54
00:03:18,040 --> 00:03:22,240
with one another and we'll be storing these IDs in other tables.

55
00:03:22,630 --> 00:03:27,700
So if it changed, that could be problematic because we would have to change it in multiple places.

56
00:03:27,880 --> 00:03:33,490
Anyways, all I'm trying to say is that we removed four, but we still have five, six and seven.

57
00:03:33,700 --> 00:03:34,930
This didn't change to four.

58
00:03:34,960 --> 00:03:36,610
This didn't change to five and so on.

59
00:03:36,790 --> 00:03:37,390
All right.

60
00:03:37,390 --> 00:03:39,100
So we deleted that rest.

61
00:03:39,100 --> 00:03:43,150
And I mean, enjoy your new life with your happy family egg.

62
00:03:43,540 --> 00:03:49,360
So one last thing here is that if you look at what I've written here, delete from cats, what do you

63
00:03:49,360 --> 00:03:50,380
think that will do now?

64
00:03:50,380 --> 00:03:53,320
I don't run it, but what do you think this would do?

65
00:03:54,310 --> 00:03:56,860
And the answer is that it deletes everything.

66
00:03:56,890 --> 00:03:57,850
All cats.

67
00:03:58,450 --> 00:04:00,610
They're all released to their happy families.

68
00:04:01,300 --> 00:04:04,300
So that's important to know that you can do that.

69
00:04:04,540 --> 00:04:09,610
So it's different than dropping a table because dropping a table gets rid of the table entirely and

70
00:04:09,610 --> 00:04:10,710
all data inside of it.

71
00:04:10,720 --> 00:04:15,220
But deleting from cats just deletes the entries in the cat's table.

72
00:04:15,220 --> 00:04:16,600
But we still have the shell.

73
00:04:16,600 --> 00:04:18,070
We still have the cat's table.

74
00:04:18,220 --> 00:04:21,100
There shouldn't be very many times when you need to use this command.

75
00:04:21,519 --> 00:04:26,560
I'm not going to do it just yet because I want us to have our cats data for a little bit for the next

76
00:04:26,560 --> 00:04:27,490
exercises.

77
00:04:27,760 --> 00:04:31,750
But just keep that in mind because we didn't specify a where clause over here.

78
00:04:32,800 --> 00:04:35,230
It's the same as selecting without a where clause.

79
00:04:35,230 --> 00:04:37,720
It selects everything, so this will delete everything.

80
00:04:38,920 --> 00:04:41,200
Next up, you're going to practice deleting.