March 11, 2008 at 9:20 am
We have a name and name_address table in our iMIS database. The name_address table stores three seperate email address purposes for an individual; company, home and other. The name table contais the email address associated with the company purpose. We have a third party application for our email marketing that only pulls from the name table and thus only the company purpose email. We have several records that have a preferred email address that is not the company email address and I would like to update the name_address table where the purpose is company and the preferred address is not company. I know how to do a simple update query where the value is specificed but we are looking at about a thousand records. How can I update a table to copy the value from one to the other.
I would imagine it would look something like the following but I just can't seem to get the syntax correc. Any help is much appreciated.
update name_address
set name_address.email =(this is the part I don't know)
(name_address.email needs to be copied from the name_address table where purpose='Home' to the name_address table where the purpose='Company')
and preferred_mail='0'
March 11, 2008 at 9:35 am
It would help if you sent the DDL for your tables. I thought you want to update email in [name] table from [name_address] as this third party app is using only this address?
Piotr
...and your only reply is slàinte mhath
March 11, 2008 at 9:43 am
Mike Feuti (3/11/2008)
...update name_address
set name_address.email =(this is the part I don't know)
(name_address.email needs to be copied from the name_address table where purpose='Home' to the name_address table where the purpose='Company')
and preferred_mail='0'
you've already done the hard part by explaining it. now just write your explanation as sql.
update name_address
set name_address.email = other.email
from name_address join name_address as other
on name_address.{key} = other.{key}
where name_address.purpose='Company'
and other.purpose='Home'
and name_address.preferred_mail='0'
March 11, 2008 at 10:03 am
Piotr ,
I'm a little new at this and don't know what you mean by DDL for the tables. Sorry. In updating the name_address table where the purpose is company that is the same as updating the name table email address (I thought). At least those two fields are always identical. But in some further testing it is the both the name_address.email where purpose='company' and the name.email that needs to be updated to the name_address.email where purpose='other'
Antonio,
That ran fine but didn't update the test record that I'm using to see if it works. Is there something else I can send or give you to help you to help me?
This is what my select looks like on my test record:
Preferred name.email name_address email purpose
0 testc@home.com testc@comp.com Company
1 testc@home.com testo@other.com Other
0 testc@home.com testh@home.com Home
So i need the testo@other to overwrite the testc@comp.com. Does that make sense?
March 11, 2008 at 10:26 am
Mike Feuti (3/11/2008)
This is what my select looks like on my test record:Preferred name.email name_address email purpose
0 testc@home.com testc@comp.com Company
1 testc@home.com testo@other.com Other
0 testc@home.com testh@home.com Home
So i need the testo@other to overwrite the testc@comp.com. Does that make sense?
the logic from your intial post won't produce those resuts.
update name_address
set name_address.email =(this is the part I don't know)
(name_address.email needs to be copied from the name_address table where purpose='Home' to the name_address table where the purpose='Company')
and preferred_mail='0'
the purpose='Home' record's email is testh@home.com so testo@home.com won't be the final value. please post the SQL statement that produces the result set in your example.
March 11, 2008 at 10:35 am
Right sorry about that. The select statement is:
select preferred_mail, name.email, name_address.email,purpose from name,name_address
where name.id=name_address.id
and name.id='405691'
I accidentaly said the 'other' purpose instead of the 'home' purpose in my previous post.
March 11, 2008 at 10:52 am
Mike Feuti (3/11/2008)
Right sorry about that. The select statement is:select preferred_mail, name.email, name_address.email,purpose from name,name_address
where name.id=name_address.id
and name.id='405691'
I accidentaly said the 'other' purpose instead of the 'home' purpose in my previous post.
take a minute and re-explain exactly what you want to do. your first post wanted to copy the address from the purpose='Home' record to the purpose='Company' record.
when doing an update, you can preview what will happen by replacing the update columns with a select:
--update name_address
-- set name_address.email = other.email
select name_address.email, name_address.purpose,
other.email as other_email, other.purpose as other_purpose
from name_address join name_address as other
on name_address.id = other.id
where name_address.purpose = 'Company'
and name_address.preferred_mail = '0'
and other.purpose = 'Home'
and name_address.id='405691'
now you can clearly see what's going to be updated.
March 11, 2008 at 11:02 am
I really appreciate all your help and patience with this.
What I need to do appears to be in two steps now.
1) Update the name_address table.
The name_address.email where name_address.purpose='Company' needs to be overwritten by the name_address.email where name_address.purpose='Home'
2)Update the name table
The name.email address is automatically populated by the name_address.email where name_address.purpose='Company' when manually entered. However when updated via a script the name.email address does not get updated.
I hope I worded this so it makes sense.
Thank you,
March 11, 2008 at 11:14 am
the update in my prior post will do step #1. it's easily modified to do step #2.
--update name
-- set name.email = other.email
select name.email,
other.email as other_email, other.purpose as other_purpose
from name join name_address as other
on name.id = other.id
where other.purpose = 'Company'
and name.id='405691'
if you want to keep these tables in-sync, you may want to consider using a trigger.
create trigger {trigger_name} on name_address
after update
as
begin
if (update(email))
begin
-- automatically set name.email to email address of company record
update name
set name.email = inserted.email
from name join inserted on name.id = inserted.email
where inserted.purpose = 'Company'
end
end
March 11, 2008 at 11:20 am
Thank you so much. This worked perfectly. Now to spend some time looking this over and learning it. You have been very helpfu.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply