• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Can you use wildcards in update statements using mysql?

 
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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%';
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic