Lesson 26

Delete Row from SQL Database Table

Learn how to use Structured Query Language (SQL) to delete records from a database.

In particular, the lesson teaches you the DELETE FROM statement with a WHERE clause to delete a row by the primary key identifier column.

It is important to be very careful going about deleting records, so for most cases, make sure to always add a WHERE clause matching the value of a unique column, since you know only one row will be deleted versus potentially deleted unwanted records by mistake.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

Summary

Deletion in Databases

In this discussion on deletion in databases, key points are highlighted regarding how to safely delete records.

Key Points

Caution with Deletion:
- Deleting records must be done carefully to avoid unintentionally deleting all data in a table.
- For example, the command DELETE FROM customers; will remove every record in the customers table.
Using WHERE Clause:
- It’s crucial to specify a WHERE clause to limit the deletion to specific records.
- Example:
```
DELETE FROM customers WHERE customer_id = 6;
```
- This command deletes the record where the customer ID is 6.
Deleting Multiple Records:
- While you can delete records based on other criteria (like last name), caution is advised.
- Example:
```
DELETE FROM customers WHERE last_name = 'Long';
```
- This could potentially delete multiple records if there are multiple customers with the last name 'Long'.
Best Practice:
- The safest method to delete records is by using the unique identifier (usually the primary key, like customer_id).
- This ensures that only the intended record (one row) is deleted.

In summary, always remember to use a WHERE clause to prevent accidental mass deletions and to prefer deleting by unique identifiers for safety.

Video Transcript

Let's talk about deletion. I added two records here and I don't want like them. I want to delete them, the six and seven. Now before I talk about delete, I want to remind you, be careful of delete and because you might delete everything, so I'll show you how to not delete So we're going to say delete from and then you say the table name. This gets customers. Now if I say like this, delete from customers, everything will be deleted, all the rows. So be careful using this. So you always want to put a where there to narrow it down to one thing usually. Okay let's say I want to delete from customers where the customer ID is six because I don't like six. I don't want seven. So I can do here customer ID is six. This is the most common way of deleting right by the ID. And the safest thing to do right is just delete one record if it's this. So if I do that, it's gone. The customer, the row with the customer ID six is gone. Now I could also delete by other columns. For example, I don't want seven, but I could say last day long. But have to be careful. This is not good. Okay, I'm telling you about this just for practice, but usually this is not a good thing because there could be other people named long, right? So if I meant to just delete Anna here, this very unique row, there were other people's name long there, they would also be deleted. So be very careful. You just usually just want to delete by the ID. That's the safest way because you just delete one thing because the idea is unique, right primary key. There's only one row, only one row that I eat.