How to copy value from one column and update another column

Question

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

• ypercubeᵀᴹ: Do all the strings end in '.SL75.jpg' ?
• rsn: yes :) , they do .

Answer 1

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>

Answer 2

Please try this.

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