Updating multiple mysql rows where column has specific value

Question

I have 1 CSV file with 50,000 rows of data and 2 columns.

The columns are user ID's and twitter profile photos.

24525,https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png
27753,https://pbs.twimg.com/profile_images/2284174752/64pe9ctjko2omrtcij7a.png
24434,https://pbs.twimg.com/profile_images/638751551457103872/KN-NzuRl.png
19911,https://pbs.twimg.com/profile_images/1240079072/logo-mysql-170x170.png

I have a MYSQL database and am interested in updating 1 table that looks like this.

umeta_id  - primary key ( we do not have this in our csv )
user_id -  This is the first column in our csv       
meta_key -  We want to update when meta_key is 'twitter_photo'   
meta_value - twitter profile photos go here

I can update 1 row with query like this

UPDATE `wp_usermeta`
SET meta_value='https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png'
WHERE meta_key LIKE 'twitter_photo'
AND user_id='24525';

I have been struggling to figure out how to do multiple rows. I tried this but it does not work.

 UPDATE `wp_usermeta` SET `meta_value` = CASE `user_id`
                      WHEN 24525 THEN 'https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png'
                      WHEN 27753 THEN 'https://pbs.twimg.com/profile_images/2284174752/64pe9ctjko2omrtcij7a.png'
                      WHEN 24434 THEN 'https://pbs.twimg.com/profile_images/638751551457103872/KN-NzuRl.png'
                      ELSE `meta_value`
                    END,
         WHERE `user_id` IN (24525, 27753, 24434) AND `meta_key` LIKE 'twitter_photo';

Any help in the right direction would be much appreciated.

Answer 1

create table temp with two fields your have in your csv and

load data infile '/path/your.csv' into table temp FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

then perform join

update wp_usermeta, temp set wp_usermeta.meta_value=temp.meta_value where wp_usermeta.user_id=temp.user_id;