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

Sin Ieng Mou
Sin Ieng Mou
822 Points

Can I use the keyword IN for a group of wild card texts?

In this SQL playground, the third practice question asks us to remove actors with the first name of "Yuri" , "Walter" and "Victor".

I know the following query can do the work, where it will delete all rows with the three first names stated in the question: DELETE FROM actors WHERE name LIKE "Yuri%" OR name LIKE "Walter%" OR name LIKE "Victor%";

but at the same time I'm thinking whether or not we could save up some time and use the keyword IN for these wildcard first names....like: DELETE FROM actors WHERE name IN ("Yuri%", "Walter%", "Victor%");

Thanks for helping!

5 Answers

Tyler Brandt
Tyler Brandt
4,717 Points

but at the same time I'm thinking whether or not we could save up some time and use the keyword IN for these wildcard first names....like: DELETE FROM actors WHERE name IN ("Yuri%", "Walter%", "Victor%");

Hi Sin,

The simple answer is no, you can't use the LIKE and IN operators together.

In your example of a potential shortened query, you omit the LIKE operator, which you need to search for patterns and use wildcards like "%." The query below will not work.

DELETE FROM actors
WHERE name LIKE IN ("Yuri%", "Walter%", "Victor%");

Unfortunately, you will have to write out each LIKE statement. The query below is correct and will work.

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

For more information, check out this stackoverflow thread.

MIkhail Vinogradov
MIkhail Vinogradov
2,666 Points

hi, but what happens to 12992 Yuriko Ishida? when we use "Yuri%". it will be deleted too! there are two different names! :)

I did:

delete from actors where name like "Yuri %" or name like "Walter %" or name like "Victor %";

To ensure the whole first name was checked.

I tried this, Sin Ieng Mou, as you probably also did. It didn't work for me.

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

Of course, this actors table is too darn long. It's very slow to load and check results to see whether the command worked. :(