CODEWITHSUNDEEP
mysql
JAVAC
JAVAC

Reputation: 1270

mysql update column values with replace

one of my table contains column path stores the URL example:\xyz\attachments, \abc\attachments, etc total i have 16 combinations to replace

i found rows by using rlike in where clause 'abc|xyz|'

have to update xyz with xxx or abc with yyyy

i am not sure how to update these part of the values of column. Is it possible using single query or i have to write 16 queries to do that? please advise here

Upvotes: 0

Views: 59

Answers (1)

Marc B
Marc B

Reputation: 360872

This is not reliable, but is doable. Basically nested replace() calls:

UPDATE ... 
SET yourfield = REPLACE(REPLACE(yourfield, '\\xyz', 'newtext'), '\\abc', 'othertext')

Note that if xyz or abc can appear in multiple places in either string, you may end up replacing something that shouldn't have been.

Upvotes: 2

Related Questions