0

I made a mistake, I set a column to NULL for 3 Millions rows. The column name was "LargeImageURL".

How can I now take the value from "SmallImageURL" column and REMOVE ".SL75" from it and update LargeImageURL.

SmallImageURL

https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.SL75.jpg

LargeImageURL

https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.jpg

Any help please ? I know this can be done with php but can SQL do this ?

thanks

  • Do all the strings end in '.SL75.jpg' ? – ypercubeᵀᴹ Apr 25 '17 at 12:39
  • yes :) , they do . – rsn Apr 25 '17 at 12:41
2
UPDATE yourtable SET LargeImageURL = REPLACE(SmallImageURL, '.SL75', '');

Test:

mysql> create table yourtable ( SmallImageURL varchar(100), LargeImageURL varchar(100) );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into yourtable values ( 'https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.SL75.jpg' , NULL );
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE yourtable SET LargeImageURL = REPLACE(SmallImageURL, '.SL75', '');
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from yourtable;
+-----------------------------------------------------------------------+------------------------------------------------------------------+
| SmallImageURL                                                         | LargeImageURL                                                    |
+-----------------------------------------------------------------------+------------------------------------------------------------------+
| https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.SL75.jpg | https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.jpg |
+-----------------------------------------------------------------------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
0

Please try this.

Update "tableName"
set LargeImageURL = Replace(SmallImageURL,'.SL75.','.')
"Where condition if any"

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.