Databases Modifying Data with SQL Deleting Data From a Database Review and Practice

Michael Lefkowitz
Michael Lefkowitz
12,295 Points

What's the best query to run for "Remove Actors"?

I first tried to use IN alongside LIKE, but that apparently doesn't work, so I used:

DELETE FROM actors WHERE name LIKE "Yuri %" OR name LIKE "Walter %" OR name LIKE "Victor %";

The query in the original question is the right query, the one that has a single <space> before each "%" ! To prove that, run the asker's DELETE statement, then run another SELECT without that <space>:

SELECT * FROM actors WHERE name LIKE "Yuri%" OR name LIKE "Walter%" OR name LIKE "Victor%";

You will see that it returns this:

| id    | name                 |
|-------|----------------------|
| 12511 | Victoria Beynon-Cole |
| 12512 | Victoria Charters    |
| 12513 | Victoria Horne       |
| 12514 | Victoria Karnafel    |
| 12515 | Victoria Thomas      |
| 12516 | Victoria Vetri       |
| 12992 | Yuriko Ishida        |

This means that, without that extra <space>, the wrong actors would have been deleted as well. So take care! :)

Aaron Campbell
Aaron Campbell
16,265 Points

I am glad you at least added the space before each % sign.

Chufan Xiao
Chufan Xiao
18,954 Points

I think this is the most straightforward solution

8 Answers

Mark VonGyer
Mark VonGyer
21,213 Points

Here's a tip for good practice.

Before running the DELETE script, use a SELECT script first

SELECT * FROM... 

and see what is returned. If it is what you want to delete then go ahead!

Jonathan Grieve
MOD
Jonathan Grieve
Treehouse Moderator 85,710 Points

I'm willing to bet your query worked.

The query didn't return any results but it wasn't meant to. I did a query myself and then looked for Yuri and there was no record of him. Run the query again and then look at the data in the playground. :-)

DELETE FROM actors WHERE name LIKE "Yuri%" OR name LIKE "Walter%" OR name LIKE "Victor%";
Michael Lefkowitz
Michael Lefkowitz
12,295 Points

I know it worked, I was just wondering if there was a better way to do it.

Kirill Kuptsov
Kirill Kuptsov
732 Points

DELETE FROM actors WHERE name LIKE "Yuri %" OR name LIKE "Walter %" OR name LIKE "Victor %";

Andrew Winkler
Andrew Winkler
37,726 Points

Hi, there is a better way:

DELETE FROM actors WHERE name IN ('Yuri', 'Walter', 'Victor');

Note that your original code had spaces, likely that's why it didn't work the first time. If there aren't spaces in the data set, that would likely throw and error.

Michael Lefkowitz
Michael Lefkowitz
12,295 Points

It worked fine and the spaces were intentional due to names such as Victoria and Yuriko. Your solution doesn't seem to work.

I think this would only account for the field holding the exact name. You need to use regex characters.

I was also looking for a better solution than repeating "name LIKE" 3 times, and, after seeing, as you did, that the LIKE IN combination didn't work, found the REGEXP expression. It would go like this:

DELETE FROM actors WHERE name REGEXP '^Yuri|^Walter|^Victor';

This, however, won't work, because the SQL playground won't recognize REGEXP as a valid function. I haven't tried it in a real MySQL database, so I can't be sure that it would work, but supposedly it would.

BTW, ended up using a statement like yours. It's repeating, but works.

Carl Peters III
Carl Peters III
701 Points

Easiest way I came up that works with was:

DELETE FROM actors WHERE name LIKE "Yuri%" OR "Walter%" OR "Victor%";

Hi! Here is my way:

DELETE FROM actors WHERE name LIKE "Yuri%" AND "Walter%" AND "Victor%";

Kam Soon Cheng
Kam Soon Cheng
1,275 Points

Hi everyone, here my way:

DELETE FROM actors WHERE name = "Yuri%" OR "Walter%" OR "Victor%";

Did it work?