Copying data between tables

[matstone98]

I have just completed the process of exporting my companies main mailing database into a new database file which is setup and structured differently. I am trying to figure out how to run a coldfusion query that can update my contact information from one table into another, tables are as follows:

companies table
companyid | contacttitle1 | contactname1 | contactsurname1 | contactposition1 | mail1
----------------------------------------------------------------------------------------------------------------------------
autonum | text | text | text | text | text

AddxContacts table
id | contacttitle1 | contactname1 | contactsurname1 | contactposition1 | mail1
----------------------------------------------------------------------------------------------------------------------------
number | text | text | text | text | text

i need to update the fields in the first table with the fields in the second table, the companyid & id from each table are the primary keys that match up the records, however the AddxContacts table may contain more than one entry with the same id no, but i only want to copy one record for each id no.

I have scoured various forums looking for an easy way to do this but so far have come up blank, any help would be greatly appriciated. Thanks

[Dan_Bracuk]

The easy way is to make your company name look like this:

companyid | companyname | more_stuff_about_the_company

Then add a companyid field to the contacts table and populate it accordingly. This, of course, is based on the premise that a contact can only belong to one company.

[matstone98]

Thats how I intend to populate the table, but how would I write an SQL statement that achieves this, I have tried doing a few tests and ended up populating each field with every contact on the database, which ended up crashing coldfusion?

[Dan_Bracuk]

There is a major difference between what you say you intend to do and what your sample code indicates you are attempting to do.

Do not store contact information in your company table. Put it elsewhere with either a one to many or many to many relationship set up, depending on your business requirements.