March 6, 2007 at 11:24 pm
This is new to me so please bear with me. I am looking to merge missing info into (Notes and Position) from Table 2 into Table 1
Customers Table 1
ID Number Contact# Name First_Name Position Address City Zip
123 0 Microsoft 123 Money St. Seattle 23320
123 1 Gates Bill
Customers Table 2
ID Contact# Name First_Name Position Address City Zip
678 0 Microsoft 123 Money St. Seattle 23320
678 2 Gates Bill God
765 0 Chrysler
765 4 Gates Bill Janitor
Notes Table 2
ID Contact# Note date Note Text
123 0 March 5, 2007 Purchased Denmark
123 1 March 7, 2007 New Wig arrived
Companies and their contacts occupy different rows in the table but share the same ID.
The ID and Contact Numbers are the primary key. I tried joining on the
First and Last Names since that is all that distinguishes contacts in the row but that is obviously not enough.
How do I get it to compare the the Company names and address book info as well. How do I update the Position in Table 1 with the corresponding values in Table 2 and the ID and contact number in notes Table 1 with the correct values in Notes table 2? Thanks for any help
March 7, 2007 at 3:38 am
Hi
Assuming you have the right fields in your customer table, then getting the notes is a simple matter of joining on your unique identifier (the aggregate key of ID and contact #) something like:
UPDATE Customers
SET [Note Text] = n.[Note Text]
FROM Notes n
INNER JOIN Customers c
ON c.[ID Number] = n.[ID Number]
AND c.[Contact #] = n.[Contact #]
Use the same theory with updating the position field in table 1 but this time you need to use the ID, contact, name and first_name columns in the join - use whatever you have to to uniquely identify a row. This is based on the assumption that the ID values will be the same in customer tables 1 and 2 (i.e. ID in table 2 is a foreign key from ID in table 1). If they are not, then there's not a lot you can do because you have no way to guarantee that Bill Gates in table 2 with ID 678 is the same Bill Gates in table 1, regardless of the company and the address because there might just be a maintenance man at Microsoft with the same name as the head honcho!
If at all possible, I would alter that schema quite significantly to include a proper primary key in each table (using aggregate vaues as primary keys is clunky and not good practice - something like an autonumbering integer field would be fine). I would also normalise the design so that companies and their contacts were in seperate tables with proper primary and foreign key constraints. Notes should be in a seperate table if you want to have multiple notes for a given company or contact, and you would probably need two tables if you want notes for companies AND notes for contatcts. This would make your table structure a bit like this:
Customers (PK custId int identity)
|
|_[FK custId]__CustomerContacts (PK contactId int identity)
| |
| |_[FK contactId]__ContactNotes (PK contactNoteId int identity)
|
|
|_[FK custId]__CustomerNotes (PK custNoteId int identity)
This would be a much easier model to deal with because the use of explicit, simple keys means you guarantee the lineage of data, i.e. you can explicitly see the ancestry of a datum and you can enforce referential integrity. Without this, your data is highly suspect and is as good as lost. It also makes it a lof easier to write queries against. If you can't change the schema, well it's all a bit academic really, but if you can then I would strongly advise you do!
Data which does not have a proper primary key is extremely hard to work with and you instantly reduce the performance of queries against it very significantly, so it is always worth spending a bit of extra time thinking about your data model at the start. Obviously, if this is a model you have inherited and you can't change it then you have my sympathies!
Hope this helps
Steve
March 7, 2007 at 10:06 am
Thanks Steve. The problem I didn't make clear was that table 1 and table 2 are in 2 different databases. Thus the PK's (ID & Contact#) are different for the Companies and Contacts. Companies in each database share the same ID as their contacts and have a Contact# of O.
While the Companies in each database can be joined on many fields other than the PK (Eg Name, Address, City), the Contacts exist in separate Rows and the only commonality is first and last name
Because the same first and last names can exist as contacts in several databases, joining on just these 2 fields will produce erroneous updates
Any ideas?
March 7, 2007 at 11:00 am
Unfortunately, I don't think there is anything you can do because, as you rightly point out, the names can exist in many places with no guarantee that they are actually the same person, which is why it is so important to have proper keys and normalised data. I'm not sure there really is a solution to your problem in this case simply because there is no link between the data sets, which is probably not what you want to hear, but if no link exists which guarantees a relationship then the best you can do is hope that the matches you make are correct which they evidently aren't.
The only alternative is a tedious and time consuming manual data matching exercise and a schema change to prevent this problem in future, if you intend to do carry out this operation again.
Sorry I can't offer you any better news!
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply