469,076 Members | 1,520 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

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 1621
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
ronverdonk
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
miller
1,089 Expert 1GB
Agreed.

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
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.