Help with Update Query

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

  • 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

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

  • 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?

  • 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.

  • 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.

  • 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.

  • 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,

  • 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

  • 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