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

enter image description 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.

• ypercubeᵀᴹ: Seems like a correct query. Is the user_id an integer or varchar?
• JediTricks007: Integer - bigint(20)

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;

• JediTricks007: Hey thank you very much. This is great and pointed me in right direction. This is very simple solution and works well. Only issue is that 1 user ID can have many meta values so I had to make the query little more specific otherwise it overwrote all the other meta values which are unrelated to the twitter photo. UPDATE wp_usermeta, temp SET wp_usermeta.meta_value = temp.meta_value WHERE wp_usermeta.user_id = temp.user_id AND wp_usermeta.meta_key LIKE 'twitter_photo'; Feel free to update that second part and will accept answer.