Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to mass change (120) subscriber passwords. The passwords are stored in table wp_users but I need to change the password based on information from another table wp_usermeta where it has information about what types of users there are.

I've been able to create this:

    UPDATE `wp_users` SET `user_pass` = MD5('default') WHERE `wp_usermeta` AND meta_value LIKE `%subscriber%` 

But I'm lost after WHERE since sql thinks I'm looking for a column called wp_usermeta when its actually a table.

    #1054 - Unknown column 'wp_usermeta' in 'where clause'

Any Ideas? Thanks


    UPDATE `wp_users` 
    SET `user_pass` = MD5('default') 
    WHERE EXISTS ( SELECT `meta_value` FROM `wp_usermeta` 
    WHERE `meta_value` LIKE '%subscriber%' )

But it changed all rows in the wp_users table. Any help appreciated. Thanks.

share|improve this question

closed as too localized by Martin Smith, JNK Jul 3 '12 at 13:03

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

UPDATE `wp_users` u, wp_usermeta m
SET u.user_pass = MD5('default') 
WHERE m.meta_value LIKE '%subscriber%' 
share|improve this answer

Your WHERE clause is incorrect - you refer to 'wp_usermeta' but don't compare it to anything... Do you mean, perhaps WHERE wp_usermeta LIKE '%subscriber%' AND meta_value LIKE '%subscriber%'?

share|improve this answer
Perhaps. But It appears my where clause is still looking for a column within wp_users, when actually its not a column. wp_usermeta is a table with a column meta_value. I can't remember how to make it search for meta_value column in a different table. It's been a year or two since I've been in phpMyAdmin ;/ – Gina Jun 8 '12 at 17:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.