Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can you use wildcards in update statements using mysql?

 
Kim Kantola
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All, I am trying to do an update on a mysql table where I have some rows of data that need to be fixed. The data in one column was entered as '01000xxx' and is a String value. I want to change all rows that have a column which begins with '01000' to be '01111'. So, for example, a value of 01000abc would be updated to be 01111abc.
Here is what I am trying but I am getting a SQL Error.

update lightpaktable set serialNumber = '0111'+RIGHT(serialNumber,4) where serialnumber like '01000e%'

I have also tried
update lightpaktable set left(serialNumber,4) = '0111' where serialnumber like '01000e%'

Any idea's ?
 
Kim Kantola
Ranch Hand
Posts: 276
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I just figured this out from something I saw on another site. This seems to do the trick:

update lightpaktable SET serialnumber = REPLACE (serialnumber, '0100', '0111') WHERE serialnumber LIKE '01000e%'

or more generically:

update table_name SET col_name = REPLACE (col_name, 'old text', 'new text') WHERE post_text LIKE 'This is old text%';
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic