writing sql script to update fields

[rrmcguire]

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

 

[Andrzejek]

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.

 

[rrmcguire]

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

thanks

 

[simian336]

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

 

[rrmcguire]

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

 

[Andrzejek]

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.

 

[rrmcguire]

yes if the email is null

 

[rrmcguire]

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')

 

[rrmcguire]

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.

 

[Andrzejek]

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.

 

[rrmcguire]

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

 

[rrmcguire]

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

 

[rrmcguire]

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')