Update query is updating millions of records and taking too long to load

[Mohibullah]

Hi everyone,



I'm trying to update a main table from a temporary table. The amount of records in the main table are about 1500, the amount usually being brought over from the temp table is about 700. I used a standard update query, following this format per column;

Field: Field1
Table: Table1
Update To: Table2.Field1

It takes a very, very long time to update, then asks if I'm sure I want to update 2,558,774 some odd records.

Any advice?

Thanks

[pbaldy]

What's the sql? Without a join, you'll get a cartesean product.

[Mohibullah]

What's the sql? Without a join, you'll get a cartesean product.

Hi pbaldy, here is the Sql code. I'm updating to fields in the table Project List_Local, from the table SapToAccess.

UPDATE SapToAccess, [Project List_local] SET SapToAccess.[Engineer (Initials)] = [Project List_local].[Engineer (Initials)], SapToAccess.Notification = [Project List_local].[Notification], SapToAccess.[Work Order] = [Project List_local].[Work Order], SapToAccess.[PM planner group] = [Project List_local].[PM planner group], SapToAccess.[Notification type - Key] = [Project List_local].[Notification type - Key], SapToAccess.[Created on] = [Project List_local].[Created on], SapToAccess.[Order type - Key] = [Project List_local].[Order type - Key], SapToAccess.[PM Order - Key] = [Project List_local].[PM Order - Key], SapToAccess.[User ID] = [Project List_local].[User ID], SapToAccess.[Work center - Key] = [Project List_local].[Work center - Key], SapToAccess.[Work center] = [Project List_local].[Work center], SapToAccess.[SVCC Indicator] = [Project List_local].[SVCC Indicator], SapToAccess.[PSPM Task] = [Project List_local].[PSPM Task], SapToAccess.[Max PSPM Start] = [Project List_local].[Max PSPM Start], SapToAccess.WBS = [Project List_local].[WBS];

[JoeM]

As pbaldy said, you need to join the two tables in this query. Otherwise you will have a Cartesian product, which would explain the explosion of records you are seeing.
Without a join, every single record form SapToAccess is being joined to every single record from [Project_List_Local].

So, if you have 1500 records in one, and 700 records in the other, you would be doing 1,050,000 record updates (1500 x 700)!
I am pretty sure that is not what you want! You don't want every single record from one table updating every single record from the other, do you?
Most of the changes will be overwriting each other.

Surely, there must be some relationship between these two tables that you need to include in your query so you only update the appropriate records with the appropriate values.

[pbaldy]

I'll get out of the way.

[JoeM]

I'll get out of the way.

No need to! Your knowledge is superior to mine, and your input is always valued!

I was just following this one along since early this morning, and reiterating what you said in a little more detail (since he did not seem to address what you said, I thought maybe he did not understand what a Cartesian Product was).

[Mohibullah]

As pbaldy said, you need to join the two tables in this query. Otherwise you will have a Cartesian product, which would explain the explosion of records you are seeing.
Without a join, every single record form SapToAccess is being joined to every single record from [Project_List_Local].

So, if you have 1500 records in one, and 700 records in the other, you would be doing 1,050,000 record updates (1500 x 700)!
I am pretty sure that is not what you want! You don't want every single record from one table updating every single record from the other, do you?
Most of the changes will be overwriting each other.

Surely, there must be some relationship between these two tables that you need to include in your query so you only update the appropriate records with the appropriate values.

Ah, ok. So what's the first step for the join query? Do you mean making relationships between them?

[JoeM]

Ah, ok. So what's the first step for the join query? Do you mean making relationships between them?

Yes.

See: http://www.techrepublic.com/article/...ccess/1043803/
Note that you can join on more than one field.

[Mohibullah]

Ah, ok. So what's the first step for the join query? Do you mean making relationships between them?

Ok. Saw that, and thank you so much. But I was wondering, is there a way to update records in another table AND append new records to that updated table?

[JoeM]

You would need two queries:
- Update queries can only update existing records
- Append queries can only add new records

[Mohibullah]

You would need two queries:
- Update queries can only update existing records
- Append queries can only add new records

How would I go about making an append query that only adds new records, and doesn't add the same data?

[JoeM]

How would I go about making an append query that only adds new records, and doesn't add the same data?

The key is the join. You join the two tables, and find which records do not have matches and add those.
Access has a Unmatched Query Wizard that will walk you through the process. Use it to create a query of the records that need adding, then change the query type to an Append Query.