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