Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

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

Posted by lfkwtz
lfkwtz
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 %";
Cristian Glodeanu
Cristian Glodeanu
4,031 Points

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,267 Points
Aaron Campbell
Aaron Campbell
16,267 Points

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

Chufan Xiao
Chufan Xiao
18,955 Points
Chufan Xiao
Chufan Xiao
18,955 Points

I think this is the most straightforward solution

8 Answers

Mark VonGyer
Mark VonGyer
21,239 Points
Mark VonGyer
Mark VonGyer
21,239 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 91,253 Points
Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 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%";
lfkwtz
lfkwtz
12,295 Points

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

Petrov von Petrov
Petrov von Petrov
21,916 Points
Petrov von Petrov
Petrov von Petrov
21,916 Points

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.

Kirill Kuptsov
Kirill Kuptsov
732 Points
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,739 Points
Andrew Winkler
Andrew Winkler
37,739 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.

lfkwtz
lfkwtz
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.

sradms0
.a{fill-rule:evenodd;}techdegree seal-36
sradms0
Treehouse Project Reviewer

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

Carl Peters III
Carl Peters III
701 Points
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%";

Ilya Zaberezhnyy
Ilya Zaberezhnyy
5,024 Points
Ilya Zaberezhnyy
Ilya Zaberezhnyy
5,024 Points

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
Kam Soon Cheng
Kam Soon Cheng
1,275 Points

Hi everyone, here my way:

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

Christian Higgins
Christian Higgins
15,758 Points

Did it work?