1

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.

  • Seems like a correct query. Is the user_id an integer or varchar? – ypercubeᵀᴹ Dec 3 '15 at 9:49
  • Integer - bigint(20) – JediTricks007 Dec 3 '15 at 18:35
2

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;
  • 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. – JediTricks007 Dec 3 '15 at 18:37

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.