" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
|
Generally UPDATE statement is used to update a field value completely. But there may be situations when we need only part of the field value to be replaced with some other values. So in this case we can’t use the traditional UPDATE statement as it may not find all the matches for our search and update. In case of MySQL, we have a ‘string replace’ function which can be used in the UPDATE statement. Syntax:
UPDATE `tablename` SET `columnname` = REPLACE(`columnname`, ‘old_value’, ‘new_value’) WHERE `columnname` LIKE ‘%old_value%’
|
|
Example:
UPDATE `events` SET `event_venue` = REPLACE(`event_venue`, ‘Mickey’, ‘Micky’) WHERE `event_venue` LIKE ‘%Mickey%’
In the above example, I want to update the event venue names which have ‘Mickey’ in it with ‘Micky’. In case, the venue name column included two more values such as “Mickey Mouse” and “Mickey House”, a normal update statement couldn’t have done it.
This tip demonstrates the string replace function of MySQL, other databases may have string replace functions with similar or slightly different names.
|