472,133 Members | 1,169 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

Find and replace functionality an in update?

I am looking for the syntax to add information to information that is already in a field. I am trying to add or replace a value to a field (org_perm_id) for those that have not posted in 30 days on a message forum.

Expand|Select|Wrap|Line Numbers
  1. UPDATE ibf_members;
  2. SET org_perm_id = ',8,12,'
  3. WHERE last_post < timestampadd(SQL_TSI_DAY, -30,
  4. current_date()) 
Is there anyway to say: set org_perm_id to just ADD 12 (I haven't posted in 30 days mask) to the existing values?

So if our members and their respective masks (masks used to visit private forums) are: (with 6 being a member in good standing):

Tara 6, 9, 10
Sue 6, 8, 9, 10
Gina 6, 10

I want to change their masks to be:
Tara 9, 10, 12
Sue 8, 9, 10, 12
Gina 10, 12

with a find "6" and replace with "12"??? Is there find and replace functionality in SQL in a field or just the ability to add one value to a field without changing the existing data?
Oct 23 '06 #1
3 1746
Any hope with something like this? I've been googling my little brains out.

UPDATE emp set designation=replace(designation, 'Sales', 'Marketing') where department='Marketing'
Oct 23 '06 #2
4,258 Expert 4TB
Yes, you could, but what if you don't want to replace only, but also concatenate? Maybe then you could use a combination of MySQL CONCAT() and REPLACE() functions (I am not that familiar with the latter one).

I think it may be easier to use a programming language (such as PHP) to SELECT the row, 'explode' the values, replace them individually, 'implode' them and UPDATE them back into the row again.

Ronald :cool:
Oct 23 '06 #3
1,089 Expert 1GB

This is something that you could use a REGEXP to translate, but this is not the best way of handling your sql table. Instead following ronverdonk's advice and just programmatically solve this: splitting the field into it's parts, maninpulating the data to add your "12" mask, and then updating the database record.
Oct 23 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Craig Keightley | last post: by
25 posts views Thread by Neo Geshel | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.