I'm using phpMyAdmin to add some new user records to the existing users within a WordPress database. The database is for a WordPress Multisite network.

I had to migrate my old WP site to the new site, but the old one was a single WP database structure, and the new WP multisite has three new records for each user.

My question is, how do I update/insert the thousands of existing user records with these three new user records for WP multisite?

Here's the three new records for the wp_usermeta table:

(225579, 7520, 'locale', ''),
(225583, 7520, 'primary_blog', '3'),
(225584, 7520, 'source_domain', 'example.com'),

Without these records added, each individual blog cannot see their own customers. In WP multisite, all the users and user meta is consolidated into one table.

To start, my old customer records can be found in the wp_usermeta table as:

(225571, 7520, 'first_name', 'John'),
(225572, 7520, 'last_name', 'Doe'),

Now I need to add the 3 records above to each existing user_id. As you can see in my example, the user_id is the same for all 5 records. Keep in mind that I'm using the data from a new user to highlight what I need. I have to add the 3 new records to each existing user_id from nothing.

I've tried a few things with no success:

INSERT INTO wp_usermeta SELECT DISTINCT meta_key FROM wp_usermeta VALUES ('source_domain', 'example.com');
INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES ('primary_blog', '3');
INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) SELECT DISTINCT umeta_id, user_id, 'source_domain', 'example.com';
INSERT INTO wp_usermeta (meta_key, meta_value) SELECT 'souce_domain', 'example.com';
INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) SELECT umeta_id, user_id, 'source_domain', 'example.com' WHERE user_id >= '51'
INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) SELECT DISTINCT (umeta_id, user_id, 'source_domain', 'example.com') WHERE user_id = user_id
INSERT INTO wp_usermeta VALUES (umeta_id, user_id>='51', meta_key='source_domain', meta_value='example.com') ON DUPLICATE KEY UPDATE user_id;

Table and Column Names:

Table = wp_usermeta
Columns = umeta_id, user_id, meta_key, meta_value
  • you may want to post this question on the main stackoverflow board with a title of MYSQL adding multiple new (key => value) for every unique ID or something like that to convey it. May get a faster answer. That being said, this could be done programmatically as well. look at WP_User_Query & WP_Meta_Query if so inclined.
    – hwl
    Jun 3 '17 at 4:57
  • Thank you. I tried on dba.stackexchange, but after 3 days hardly anyone looked at it. I figured since this is Wordpress, this would be the place.
    – ina2n
    Jun 3 '17 at 5:33
  • Ah no worries. Somebody here very well may know. If I get a minute I'll try to mock up a different approach with WP_User_Query, but I wouldn't be much more than fresh eyes on the same dilemma MySQL-wise.
    – hwl
    Jun 3 '17 at 14:43
  • I would prefer to find a phpMyAdmin solution, but I know not everyone is comfortable with this. For me, I prefer to inspect the results after running tests on a duplicate of the original database. By the way, I hope everyone makes a duplicate database and hammers away on the dup.
    – ina2n
    Jun 4 '17 at 2:45

here is Query. Try this, don't forget to replace user_id with actual userid, meta key with your meta_key like "phone_no" and meta value with your meta_value like "1238899776".

INSERT INTO `wp_usermeta`(`user_id`, `meta_key`, `meta_value`) 
VALUES (1375,'_lastlogin', '1497621956')`

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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