Pick up one record of those with a dup foreign key 2

[icodian]

Morning, I'll get right to it. I only use SQL occasionally so hopefully this is an easy question for more of you.

I have an Account table and a Contact table. Multiple Contacts per Account. I want to move data from the Contact table to the Account table such as email address and fax number.

For those Accounts that have only 1 contact, I have successfully inserted that contacts info into the Account.
However, I am having problems with those Accounts that have multiple Contacts.

If an Account has 5 Contacts, I actually don't care which of those Contacts I pull the data from...it's a long story. The only thing I need to do is select only 1 Contact record that contains that AccountID and move the data over.

This is a one time transfer and will not need to be run ever again.

Thanks for any help!

 

[JohnDTampaBay]

UPDATE a
SET Email = c.Email, Fax = c.Fax
FROM Accounts a
INNER JOIN Contacts c
ON a.AccountID = c.AccountID

If there are 5 rows in Contacts for an account, SQL will update the corresponding row in Accounts 5 times. But since there is only 1 row in Accounts, only 1 row will the updated 5 times. You never know which data from Contacts will end up in Accounts, but that's not an issue. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[icodian]

Thanks for the response John!

The theory behind this is good, but I neglected to explain part of the problem. That's my fault for trying to simplify the question and then forgetting an important piece of it. Can I use the same concept for an Insert statement? Here's why.

I actually have 2 Account tables. Account & AccountAddlInfo. These have a one to one relationship. AccountAddlInfo is basically an extension of the Account table. There is a reason for not combining them into one table, but I'd rather not explain all that.

The AccountAddlInfo table is blank right now. I must move these Contact fields into this AccountAddlInfo table. Therefore, I am inserting records into the table rather than updating the table.

Does that make sense? Hopefully I'm not leaving something else out this time.

 

[JamesLean]

Try this:

INSERT AccountAddInfo (<cols>)
SELECT <cols>
FROM Account a JOIN Contact c ON a.AccountID = c.AccountID
WHERE c.ContactID = (
    SELECT MIN(ContactID)
    FROM Contact
    WHERE AccountID = a.AccountID
  )

--James

 

[icodian]

Thanks to John and James. Both of you made suggestions that would work.

Using John's suggestion, I was going to simply to a two step process.
1) Insert records into the AccountAddlInfo table first based on the Account table so one record would be created for each account.
2) Use the Update statement to transfer the data from the Contact records.

This would have worked great. Fortunately, I saw James post as I started the changes. This idea was easier for me since I had most of the Insert statement already written. It worked perfectly.

Thanks again for your help guys!

 

[JohnDTampaBay]

In this case, James' solution is much more efficient. Glad to help. Cheers!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JamesLean]

Although admittedly I had the advantage of having the key piece of information, namely an INSERT not an UPDATE! I'm sure I would have written the exact same query as you just given the first post ;-)

--James