Updating a column in MySQL using its previous value

Question

I have a table in mysql 'url_map' with 2 columns: id, url with some values populated in it. Something like this:

+----+-----------------------------------------+ | id | url | +----+-----------------------------------------+ | 1 | http://myserver.mywebsite.com/file1.txt | | 2 | http://myserver.mywebsite.com/file2.txt | +----+-----------------------------------------+

My requirement is to update the 'url' of every row in the table to a new value using it's previous value.

+----+---------------------------------------------+ | id | url | +----+---------------------------------------------+ | 1 | https://mynewserver.mywebsite.com/file1.txt | | 2 | https://mynewserver.mywebsite.com/file2.txt | +----+---------------------------------------------+

If you see the changes, in every row, the value of 'url' has been changed from

http:// to https://
myserver to mynewserver

rest other things in the url are kept as they were previously.

Can anyone please help me out in writing the query for this ?

Replace function can be a start point – M Khalid Junaid Sep 12 '17 at 07:39 — M Khalid Junaid, Sep 12 '17 at 07:39

score 1 · Answer 1 · answered Sep 12 '17 at 07:42

1

You can use previous value directly in the update statement

UPDATE your_table t SET t.url = REPLACE(REPLACE(t.url, 'http://', 'https://'), 'myserver', 'mynewserver')

answered Sep 12 '17 at 07:42

Oleh Rybalchenko

6,998
3
22
36

score 0 · Answer 2 · answered Sep 12 '17 at 07:43

i would simply do an UPDATE statement like:

UPDATE url_map
   SET url = REPLACE(REPLACE(url, 'http', 'https'),'myserver ','mynewserver')

or if you want to do it in one REPLACE (as http and myserver is basically one string)

UPDATE url_map
   SET url = REPLACE(url, 'http://myserver', 'https://mynewserver')

score 0 · Answer 3 · answered Sep 12 '17 at 07:43

0

Well, as your example said, you can use replace method.

Update url_map
Set    url = replace(url, 'http://myserver', 'https://mynewserver');

answered Sep 12 '17 at 07:43

Víctor López

819
1
7
19

DenDen · Answer 4 · 2017-09-12T10:54:57.527

-1

I didn't test this yet, but in theory this should work:

UPDATE `url_map` SET url = REPLACE(url, "http://myserver", "https://mynewserver")

edited Sep 12 '17 at 10:54

answered Sep 12 '17 at 07:42

DenDen

34
2

Your syntax is wrong. – Víctor López Sep 12 '17 at 09:02
My bad, for whatever reason I had the "SELECT" syntax still in mind. Updated it. – DenDen Sep 12 '17 at 10:55

score -2 · Answer 5 · answered Sep 12 '17 at 07:38

-2

You have to create a procedure. In the procedure loop through the rows and do the change. See https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html for a detailed description.

answered Sep 12 '17 at 07:38

Molitoris

935
1
9
31

You dont need a procedure in this case, is much easier do a simple update. In other case, the procedure could be a good solution :) – Víctor López Sep 12 '17 at 07:46

Updating a column in MySQL using its previous value

5 Answers5