Update Query

[TedMosby]

I am trying to update a table using the query below, but for some reason it actually updates 4993086 records which is actually far more records than in the table in the first place (466339 in PARAMED_TEMP).



UPDATE PARAMED_TEMP LEFT JOIN CAREGROUPMAP
ON PARAMED_TEMP.ContactServiceID = CAREGROUPMAP.Specialty
SET PARAMED_TEMP.CareGroupViaSpec = CAREGROUPMAP.[Care Group]
WHERE (((PARAMED_TEMP.ContactServiceID) In ("DNS","HVS","COMM HEART FAILURE SERVICE","COMM PAED NURSING","COMM RESPIRATORY SERVICE","CONTINENCE","COPD NURSING","COUNSELLING","DERM","DIABETIC NURSING","INTERMEDIATE CARE NURSING","NURSING EPISODE","TISSUE VIABILITY NURSING","TRAUMA & ORTHOP")));


How could this happen?

[CreganTur]

It's because your query created a cartesian product (each row in one of your tables is being multiplied by the total rows in the joined table)- this is an error that commonly happens when there is an error with your join.

Are you certain that the fields you're joining ON have the proper relationship (generally Primary Key and Foreign Key)?

[OBP]

As Randy says that is a Typical Join problem between the Tables, you can set the Join to take all the Records from the Primary Table and only those matching from the Sub Table.
You can also set the Query Properties to Unique Records or Unique Values as well.

[TedMosby]

When I go into the query manager and double click on the Join Properties, I have slected Option2: Include ALL records from PARAMED_TEMP and only those records from CAREGROUPMAP where the joined fields are equal. Is there any other way to do this?

[OBP]

Ted, does that not reduce the number of records?
Did you look at using the Query's "Unique Records" or "Unique Values".
Have you tried that as a simple "Select" query first?
Can you post a Zipped database with those 2 tables and a little dummy data for us to test?

[CreganTur]

Sometimes, on rare occasions, a joined query will result in a cartesian product no matter what you do. If this is the case, then you'll have to use ADO Record Shaping to fix the issue. I'm on vacation and my book with the info on Record Shaping is at work, so if this is what you need to fix your problem it will be Thursday before I can post specifics... but you can always use google ;)