Need help with MySql query

Question

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")

What does this return? DB structure? Mysql error? Are you using phpmyadmin? — Shoe, Mar 24 '11 at 17:25
Thanks for your comment. I use phpmyadmin and got this >>> #1093 - You can't specify target table 'wp_usermeta' for update in FROM clause — Devyn, Mar 24 '11 at 17:31
You have to apply my advice even though someone think I'm under drugs. In order to avoid error 1093 you have to use a subquery like I've done. Apply correct field to your specific table — Nicola Cossu, Mar 24 '11 at 17:36
So what you want is to delete every row on that table or just the one with `meta_value` = `tonetone`? — Shoe, Mar 24 '11 at 17:42
@nick, I tried yr advice but got this >> 0 row(s) deleted. ( Query took 0.5661 sec ) — Devyn, Mar 24 '11 at 17:44
I've modified my answer according to your table. However it seems to me that Miky Dinescu's query does exactly what you want. I've done the things more difficult of what they are. — Nicola Cossu, Mar 24 '11 at 17:49
Please see my updated answer. It does the same as your accepted answer but without the need to create a temporary table. Generally, with SQL it's better practice to avoid creating auxiliary tables if you can. — Mike Dinescu, Mar 24 '11 at 19:57

Nicola Cossu · Answer 1 · 2011-03-24T17:48:31.223

1

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

edited Mar 24 '11 at 17:48

answered Mar 24 '11 at 17:25

Nicola Cossu

54,599
15
92
98

RolandoMySQLDBA · Accepted Answer · 2011-03-24T18:35:43.663

1

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;

edited Mar 24 '11 at 18:35

answered Mar 24 '11 at 17:31

RolandoMySQLDBA

43,883
16
91
132

Wait. I just realized, you want to delete userid's that have tonetone in any of its records. BRB... – RolandoMySQLDBA Mar 24 '11 at 17:43
Yes Rolando, it's what I want to do. Thanks! – Devyn Mar 24 '11 at 17:50
I'm getting this >> #1109 - Unknown table 'wp_usermeta' in MULTI DELETE – Devyn Mar 24 '11 at 18:08
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING (user_id)' at line 1 – Devyn Mar 24 '11 at 18:18
Wow.. I don't know what to say but it really really works. Thank you so much :) – Devyn Mar 24 '11 at 18:58

score 0 · Answer 3 · edited May 23 '14 at 09:19

0

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

edited May 23 '14 at 09:19

BenMorel

34,448
50
182
322

answered Mar 24 '11 at 17:26

Mike Dinescu

54,171
16
118
151

Thanks Miky but it's not what I want. As I explained, there are more than one record with same user_id in wp_usermeta. If I run this sql, it'll only delete on row of a user_id. – Devyn Mar 24 '11 at 17:32
2

What exactly do you mean? This query will delete all records from the table `wp_usermeta` which have the meta_value = 'tonetone'. Have you tried running this query? – Mike Dinescu Mar 24 '11 at 17:34
I've uploaded the image and as you can see, user_id has more than one record and meta_value = "tonetone" is just one row. All I want to do is delete all rows of that user_id. – Devyn Mar 24 '11 at 17:43

score 0 · Answer 4 · edited May 23 '17 at 12:01

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.

Could you please have a look at the image I recently updated? It'll make u clearly understand what I want to do. — Devyn, Mar 24 '11 at 17:45

Need help with MySql query

4 Answers4