×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

writing sql script to update fields

writing sql script to update fields

writing sql script to update fields

(OP)
I have 2 tables, payee and drs_payee. I'm trying to update the email in payee but add to the email address which is already in that field. I want to just update this email and set the field email_summary = 'Y' when the field factoring_company_id = 'OTRCCHIL' from the drs_payee table. Problem is I have an email field in payee and an email field in drs_payee both named 'email'. I have written the following script but it is not liking something with this...If someone could assist I would appreciate it


update payee
set email = p.email
from payee p
Inner Join drs_payee as d
on p.id = d.id and d.factoring_company_id = 'OTRCCHIL'
set p.email = p.email + ',user@domain.com' ,email_summary = 'Y' from payee p

RE: writing sql script to update fields

Just a guess here....

update payee set
email = email + ',user@domain.com',
email_summary = 'Y'
where id IN (select id from drs_payee
where factoring_company_id = 'OTRCCHIL')


---- Andy

There is a great need for a sarcasm font.

RE: writing sql script to update fields

(OP)
That seemed to work fine...thanks...how would I write this if the email is currently blank?

thanks

RE: writing sql script to update fields

The above code would work if email is blank or not. However you can just updated it with the new email....

update payee set
email = 'user@domain.com',
email_summary = 'Y'
where id IN (select id from drs_payee
where factoring_company_id = 'OTRCCHIL')

Simi

RE: writing sql script to update fields

(OP)
problem is I don't want to overwrite the email fields which I just appended the email to the ones which already had an email as the first script just updated email with fields which already had an email in it, the ones which were empty nothing was added

RE: writing sql script to update fields

"the ones which were empty nothing was added "
They should have ", user@domain.com" if they were empty to start with....

"if the email is currently blank?" - you mean NULL?

update payee set
email = 'user@domain.com',
email_summary = 'Y'
where id IN (select id from drs_payee
where factoring_company_id = 'OTRCCHIL')
AND email IS NULL



---- Andy

There is a great need for a sarcasm font.

RE: writing sql script to update fields

(OP)
yes if the email is null

RE: writing sql script to update fields

(OP)
so I guess in between the email and the user@domain.com which we're adding it needs to be a semi colon instead of a comma, I'm writing it as such but its complaining saying terminating quote not found

update payee set
email = email + ';user@domain.com',
email_summary = 'Y'
where id IN (select id from drs_payee
where factoring_company_id = 'OTRCCHIL')

RE: writing sql script to update fields

(OP)
just looking to find out how to write the above script putting a semicolon between the current email and the address added instead of a comma.....thanks for any help provided.

RE: writing sql script to update fields

Since the semicolon causes issues in SQL Server, I would try either:
email = email + Chr(58) + 'user@domain.com'

or parameterized query.


---- Andy

There is a great need for a sarcasm font.

RE: writing sql script to update fields

(OP)
thank you for the response, what is Chr(58)?

RE: writing sql script to update fields

(OP)
ok, just tried the above with your suggestion and it didn't seem to work, doesn't recognize Chr as a built in function name

RE: writing sql script to update fields

(OP)
tried something like

update payee set
email = email + '\; user@somain.com',
email_summary = 'Y'
where id IN (select id from drs_payee
where factoring_company_id = 'OTRCCHIL')

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close