CODEWITHSUNDEEP
mysql
Devyn
Devyn

Reputation: 2275

Need help with MySql query

I want to delete all records from wp_usermeta with user_id whose meta_value is "tonetone" which is spam accounts.

As you know already, there are many records with one user_id in wp_usermeta. I tried like this but doesn't work. Thanks to anyone who can show me the way.

All I want to do is delete all records with that user_id but the common value all I can get is "tonetone"

DELETE FROM wp_usermeta WHERE user_id = (SELECT user_id FROM wp_usermeta WHERE meta_value = "tonetone")

Upvotes: 1

Views: 223

Answers (4)

Mike Dinescu
Mike Dinescu

Reputation: 55720

The following query will delete ALL records from the table wp_usermeta which have the field meta_value set to tonetone

delete wp_usermeta 
  from wp_usermeta where meta_value = 'tonetone'

UPDATE

After having seen your updated question it's clear to me what you're trying to do. The following query will delete all records for each user who has a meta_value set to 'tonetone'.

DELETE wp_usermeta
  FROM wp_usermeta T1
INNER JOIN (SELECT DISTINCT user_id FROM wp_usermeta WHERE meta_value = 'tonetone') T2 ON T1.user_id = T2.user_id

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

If you want to delete users where any of the records for that user has tonetone as a meta_value, you can do this:

CREATE TABLE wp_usermeta_users_to_delete
( user_id BIGINT(20) NOT NULL PRIMARY KEY );
INSERT INTO wp_usermeta_users_to_delete
SELECT DISTINCT user_id FROM wp_usermeta
WHERE meta_value = "tonetone";
DELETE A.* FROM wp_usermeta A INNER JOIN wp_usermeta_users_to_delete B USING (user_id);
DROP TABLE wp_usermeta_users_to_delete;

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56357

    delete from wp_usermeta where umeta_id in (select * from (select umeta_id from wp_usermeta where meta_value = "tonetone") as t)

Upvotes: 1

Shoe
Shoe

Reputation: 76240

Your error:

#1093 - You can't specify target table 'wp_usermeta' for update in FROM clause

As documented here : MySQL Error 1093 - Can't specify target table for update in FROM clause you cannot "modify the same table which you use in the SELECT part". The following should work:

DELETE FROM wp_usermeta WHERE meta_value = 'tonetone'

Or to correct your query a little bit:

DELETE FROM wp_usermeta WHERE user_id IN (SELECT user_id FROM wp_usermeta WHERE meta_value = "tonetone")

But it has no sense at all. You basically check for a comparison in user_id while what you really want to check it the field of meta_value.

Check the first query and let me know.

Upvotes: 0

Related Questions