Issue with update using self-join (1 Viewer)

[slhuber]

I am running the following UPDATE statement on a table with about 1000 rows, using a self join:

UPDATE [Tax Report Co Date] INNER JOIN [Tax Report Co Date] AS [Tax Report Co Date_1]
ON [Tax Report Co Date].ID = [Tax Report Co Date_1].[ID+1]
SET [Tax Report Co Date].Co = [tax report co date_1].co, [Tax Report Co Date].[Date] = [tax report co date_1].date
WHERE ((([Tax Report Co Date].Co) Is Null) AND (([Tax Report Co Date].Date) Is Null));

The problem is that it doesn't update all of the appropriate rows "the first time". It leaves gaps of rows unchanged... almost as if it blocked itself from updating, or some sort of buffer/cache issue. If I run the UPDATE a second time it updates the remaining rows successfully.

Any ideas on why it takes multiple passes to perform this update? Can I do something differently?

 

[Bilbo_Baggins_Esq]

Are you absolutely certain it is leaving the rows completely unchanged?

It could be that all it is able to update is one field, and then on a second run, since the one field is now populated, it can make the other associations needed to update the rest.

 

[Bilbo_Baggins_Esq]

another idea is that if any of the incomplete rows, depend on the other rows for info, they can not populate until the first set of rows is updated.

The reason I mention that is that I notice one of your fields is ID+1.

 

[slhuber]

Basically, each row is being updated with the company and date from the row before it, with a few exceptions. So, yes, the update is dependent on other rows to be updated.

However, about 90% of the rows are updating successfully in this manner. Its just a few random ones (not the first, not the last) that don't work the first time.

I really appreciate any ideas anyone has on this!

 

[Bilbo_Baggins_Esq]

hmmm, check to ensure that the records are being updated in the sequence required to allow continious progress through the other records.

I believe that if just one record is out of sequence, and misses its update becasue of it, then all the remaining records records that would depend on that one will not be updated.

Another possibility (a long shot i admit) is how you're looking at it.
Are you running this update query while the table is open?
I'd bet this is not applicable, but if you have the table open, access can be wierd about how it shows updated records for a table that is open when it is updated.
If this applies to you, it is entirely possible that the table is actually updated even though you're not seeing it.
Again, I understand this is a long shot, but if this applies to you, after you run the first update, click into one of the columns for one of the non-updated records, and just scroll up or down with the arrow key. You may find that as soon as your cursor enters a record, it shows the newly updated value.

I know, it's a long shot, but I thought I'd mention it just in case.

 

[slhuber]

How can I ensure that they are being updated in a particular order with just a basic UPDATE statement?

 

[Bilbo_Baggins_Esq]

You would want to make sure that the underlaying table(s) are saved in the sort order that the update needs to run.

BTW, does it take more that 2 runs for this query to complete the table?

 

[slhuber]

The tables are in the order they need to be before the update. So, I don't think that's it.

I haven't seen this take more than two passes.... yet! But if the tables get larger, I'm wondering if it might eventually require more passes.