CODEWITHSUNDEEP
phpmysql
Hoyo
Hoyo

Reputation: 1074

MYSQL DELETING from Search result

I have an old DB with over 300k rows and I want to clean it up and only keep the new one, anyways I want to delete the rows which have the user_id of the results from the following query:

   SELECT user_id
   FROM wp_usermeta
   WHERE meta_value LIKE '%student%'

I have the needed user_id but they're in a column, so I used the following but it doesn't seem to be working

   DELETE FROM wp_usermeta 
   WHERE user_id = (SELECT user_id
   FROM wp_usermeta
   WHERE meta_value LIKE '%student%')

So basically, how do I used the result's user_id to delete from the same Table?

Upvotes: 0

Views: 134

Answers (3)

Oto Shavadze
Oto Shavadze

Reputation: 42753

If you have many rows, IN maybe works slow, you can use JOIN for deleting, like this:

DELETE wp_usermeta.* FROM wp_usermeta INNER JOIN ( SELECT user_id FROM wp_usermeta WHERE meta_value LIKE '%student%' ) t ON wp_usermeta.user_id = t.user_id

Upvotes: 1

ThiefMaster
ThiefMaster

Reputation: 318488

Why query the user id if you can just use the meta_value to delete the items?

DELETE FROM wp_usermeta WHERE meta_value LIKE '%student%'

Upvotes: 3

Lepanto
Lepanto

Reputation: 1413

You have to use IN operator for this search

Try something like 

DELETE FROM TABLE1 
WHERE user_id in (SELECT user_id FROM TABLE2 WHERE meta_value LIKE '%student%')

And If you have to search same table why not use the where condition directly

DELETE FROM TABLE1 WHERE meta_value LIKE '%student%'

Upvotes: 1

Related Questions