jshiffty
asked on
mysql, replace single single characters.
Okay, so I have to replace a lot of fields in my database. For additional explanation please see this question -- https://www.experts-exchange.com/questions/25019604/Mysql-combine-merge-replace-issues.html --
I'm going through and for each user I am having to choose a single 'user_id' value, delete the duplicate 'user_id' and then I had been running a replace query on my 'contacts' table to change all contacts to the new 'user_id'. My problem comes when I am trying to run the replace query on 'user_id' 's that are not 3 digits. So when running this query
UPDATE contacts SET user_id = replace( user_id, '65', '64' )
Any 'user_id' containing 65 (165, 265, 365...) will be changed to '64'
How can I properly configure my script to change these values correctly?
I'm going through and for each user I am having to choose a single 'user_id' value, delete the duplicate 'user_id' and then I had been running a replace query on my 'contacts' table to change all contacts to the new 'user_id'. My problem comes when I am trying to run the replace query on 'user_id' 's that are not 3 digits. So when running this query
UPDATE contacts SET user_id = replace( user_id, '65', '64' )
Any 'user_id' containing 65 (165, 265, 365...) will be changed to '64'
How can I properly configure my script to change these values correctly?
update contacts SET user_id=64 where user_id like '%65%'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome, thanks again Hain. Was a bit nervous after the first run had 5000 rows affected! But of course after taking a look I saw query worked flawlessly and properly filtered and changed correct fields.