Replace Part of a Record in mySQL?

Discussion in 'MySQL' started by mokimofiki, Nov 10, 2011.

0
  1. #1
    I have a huge database with a field for product type. Here is the issue:

    Say some fields are Music|TV|Voice and others are just Music|Voice and another may be just TV. How can I change all values that have TV in it to Television but keeping the other products there (i.e. Music|TV|Voice would change to Music|Television|Voice and the field with just TV would change to Television).

    There are many variations of this so its not as easy as saying UPDATE Music|TV|Voice with Music|Television|Voice etc.

    First off I have never seen this done or had a need for it so not sure if its even posible without pulling the entire database down making a mass text change and reimporting it. The issue there lies in the fact that there are over 900k entries to this database.

    Thank you in advance :)
     
    Solved! View solution.
    mokimofiki, Nov 10, 2011 IP
  2. #2
    First off, that's not a good way to store data. It bypasses the relational part of "relational database" and makes efficient searching and updating virtually impossible. You would be far better off using a separate table for the options and a second table to reference the product to multiple options.

    However, given the current structure, to do a replace you would use this:

    UPDATE my_table SET product_type = REPLACE(product_type, 'TV', 'Television');

    Make sure to do a backup before performing any update that will affect the entire table/database
     
    jestep, Nov 10, 2011 IP
  3. mokimofiki

    mokimofiki Well-Known Member

    Messages:
    444
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    130
    #3
    Thank you, I know its not the right way to have it normally but the system that the information is pulled from was setup that way and for consistency was left that way. The company this is for has a system that was created a very long time ago.
     
    mokimofiki, Nov 10, 2011 IP