Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

writing sql script to update fields

Status
Not open for further replies.

rrmcguire

Programmer
Oct 13, 2010
304
US
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
 
Just a guess here....
[tt]
update payee set
email = email + ',user@domain.com',
email_summary = 'Y'
where id IN (select id from drs_payee
where factoring_company_id = 'OTRCCHIL')[/tt]


---- Andy

There is a great need for a sarcasm font.
 
That seemed to work fine...thanks...how would I write this if the email is currently blank?

thanks
 
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
 
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
 
the ones which were empty nothing was added "
They should have [tt]", user@domain.com"[/tt] if they were empty to start with....

"if the email is currently blank?" - you mean NULL?
[TT]
update payee set
email = 'user@domain.com',
email_summary = 'Y'
where id IN (select id from drs_payee
where factoring_company_id = 'OTRCCHIL') [BLUE]
AND email IS NULL[/BLUE]
[/TT]

---- Andy

There is a great need for a sarcasm font.
 
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')
 
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.
 
Since the semicolon causes issues in SQL Server, I would try either:[tt]
email = email + Chr(58) + 'user@domain.com'[/tt]
or parameterized query.



---- Andy

There is a great need for a sarcasm font.
 
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
 
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')
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top