Results 1 to 2 of 2

Thread: if...else....

  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: if...else....

    I have 2 tables.... a contact table and an accounts table (both tables are extremely large!!!)

    I want to write a query:

    If customer_account is in contact table and contact.tele is NOT null then
    UPDATE table1
    Set tele = contact.tele,
    name = contact.contact_name
    from
    (select contact.tele, contact.contact_name
    from contacts)
    ELSE
    UPDATE table1
    Set tele = accounts.tele,
    name = accounts.contact_name
    from
    (select accounts.tele, accounts.contact_name
    from accounts)

    but not 2 sure how to write the if statement?!

    Thanks

  2. #2
    Join Date
    Sep 2005
    Posts
    19
    Need more info, but I'm guessing the account field would tie the contact and account tables together? Also, do you have some way of defining the unique name and tele in table1 for updating?

    I created three tables and lightly populated contact and account and then ran this to populate table1...

    insert into table1 (tele, name, account)
    (select case when contact.tele is not null then
    contact.tele else account.tele end,
    case when contact.tele is not null then
    contact.contact_name else account.contact_name end,
    case when contact.tele is not null then
    contact.customer_account else account.account end
    from contact, account
    where account.account = contact.customer_account)

    Next I modified the tele of a few individuals and ran this as the update...

    update table1 set tele = (select case when contact.tele is not null then
    contact.tele else account.tele end
    from contact, account
    where account.account = contact.customer_account
    and ((contact.tele is not null and table1.account = contact.customer_account)
    or (contact.tele is null and table1.account = account.account))),
    name = (select case when contact.tele is not null then
    contact.contact_name else account.contact_name end
    from contact, account
    where account.account = contact.customer_account
    and ((contact.tele is not null and table1.account = contact.customer_account)
    or (contact.tele is null and table1.account = account.account)))

    It can probably be done shorter than that, but that's how my tiny brain thought about it.

    (Sorry if the query isn't pretty, I have to figure out how to post them better)
    Abbra_doo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •