Is there a possible way to modify something same in mysql?

Question

I have these values for each record in a column :

http://192.168.106.82/bookclub/uploads/booktype/01_type_education.png
http://192.168.106.82/bookclub/uploads/booktype/02_type_religion.png
http://192.168.106.82/bookclub/uploads/booktype/03_type_technology.png
http://192.168.106.82/bookclub/uploads/booktype/04_type_business.png
http://192.168.106.82/bookclub/uploads/booktype/05_type_healthy.png
http://192.168.106.82/bookclub/uploads/booktype/06_type_magazine.png
http://192.168.106.82/bookclub/uploads/booktype/07_type_literature.png

Then I want to modify IP Address 192.168.106.82 to other such as 192.168.1.39 Can I modify all of these in one time?

score 1 · Accepted Answer · answered Jun 21 '16 at 18:40

1

You can do with update

UPDATE your_table
SET your_field = REPLACE(your_field, '192.168.106.82', ' 192.168.1.39')
WHERE your_field LIKE '%192.168.106.82%'

answered Jun 21 '16 at 18:40

ScaisEdge

131,976
10
91
107

If `your_field` is indexed, they'd probably be better off with `LIKE 'http://192.168.106.82%'` – Uueerdo Jun 21 '16 at 18:41
@Uueerdo .. could be .. .. but the problem substantially is the same .. there also other way .. not related porperly with like ... is possibile use substring .. or other way .. my is a suggestiion clear and immediatelly usable – ScaisEdge Jun 21 '16 at 18:44
Yeah, not criticizing or anything, just figured I'd add it as an sidenote. – Uueerdo Jun 21 '16 at 18:49
@Uueerdo .. yes your suggestion is reasonable .. i'm agree .. – ScaisEdge Jun 21 '16 at 18:54
I tried this statement : UPDATE books SET imageurl = REPLACE(imageurl, '192.168.106.82', '192.168.1.39') WHERE imageurl LIKE '%192.168.106.82%' But I found some errors, https://i.imgur.com/hi8wVxv.jpg a new statement was found but no delimiter between it and the previous one. (near REPLACE) – Bomb Staunchman Jun 22 '16 at 08:12
Oh sorry, it just a warning but everything worked out well. Thank you very much. :) – Bomb Staunchman Jun 22 '16 at 08:22

score 0 · Answer 2 · answered Jun 21 '16 at 18:39

You can use functions in UPDATEs' SET clauses, like so:

UPDATE table 
SET field = REPLACE(field, 'old value', 'new value')
WHERE condition

To update the entire table, you would either leave the WHERE clause off entirely, or set a condition that cannot be false (some server configs prevent WHEREless updates).

Is there a possible way to modify something same in mysql?

2 Answers2