Loop and test Child records - update parent (1 Viewer)

DKoehne · Jul 30, 2018

The_Doc_Man said:
Do this as a transaction. See this link for setting it up.

Code:

DBEngine(0).BeginTrans UPDATE TableA SET PAID = TRUE ; UPDATE TableA INNER JOIN TableB ON TableA.SettlementID = TableB.SettlementID SET TableA.PAID = TableA.PAID AND TableB.PAID ; DBEngine(0).CommitTrans dbForceOSFlush

No need for code. Doing it as a transaction stops others from seeing Table A's PAID field as TRUE from the first update until the second update resolves the issue for those cases that are not fully paid.

Further, since this will be VERY SIMPLE SQL it should be incredibly fast when compared against an explicit recordset loop.

If you weren't sure, that second UPDATE will make TableA.PAID = FALSE the first time it hits a case of TableB.PAID = FALSE.

Thanks for this suggestion. I am not having any success in getting it to work right. Please see posts (10 and 17). I am hoping to get yours to work as these two tables will be the largest two tables in operation. Your suggesting may be the most efficient ?? I'm hoping anyway. Any help getting it working right would be appreciated. Thanks much.

pbaldy · Jul 30, 2018

Post 21 was moderated, I'm posting to trigger email notifications.

June7 · Jul 30, 2018

Yes, it says 3 because of the join but in actuality only 1 record is updated. I tested both queries I suggested and they worked.

isladogs · Jul 30, 2018

DKoehne said:
I have run the update with the inner join and when the child records are 1 of 3 true, it wants to update three rows. It should only be updating one row.

Try setting unique records = Yes in the property sheet
Or in query SQL :

UPDATE DISTINCTROW tblSettlements
INNER JOIN (SELECT tblPaymentsSub.tblSettlements_SettlementID, tblPaymentsSub.Paid FROM tblPaymentsSub
WHERE tblPaymentsSub.tblSettlements_SettlementID NOT IN (SELECT tblPaymentsSub.tblSettlements_SettlementID FROM tblPaymentsSub
WHERE tblPaymentsSub.Paid=False)) AS Query1
ON Query1.tblPaymentsSub.tblSettlements_SettlementID = tblSettlements.SettlementID
SET tblSettlements.Paid = [Query1].[Paid];

DKoehne · Jul 31, 2018

June7 said:
Yes, it says 3 because of the join but in actuality only 1 record is updated. I tested both queries I suggested and they worked.

Do you believe this approach would be a more efficient approach and if so, why?
It seemed to run slower that the count(*) but I cant imagine that being good across two very large tables - thoughts?

June7 · Jul 31, 2018

I expect use of DISTINCT or DISTINCTROW will result in 'non updatable" query. I already tested my suggested query and it works without.

I am not surprised it runs slowly. Again, UPDATE is not necessary, calculate when needed.

Mark_ · Jul 31, 2018

DKoehne,

What is your definition of "Very large Tables" and what back end do you expect to use for production?

isladogs · Jul 31, 2018

June
I realise the thread has discussed whether its necessary to do updates or not.
However I was just responding to post #20

AFAIK, UPDATE DISTINCT is not valid SQL code
By contrast UPDATE DISTINCTROW often allows an update query to work when a simple UPDATE is non-updateable

DKoehne · Jul 31, 2018

Mark_ said:
DKoehne,

What is your definition of "Very large Tables" and what back end do you expect to use for production?

mySQL is the backend. I am not so much worried about size as I am about execution time on the query - might be talking about 175,000 records

The_Doc_Man · Jul 31, 2018

Just a side note here:

Without regard to the legality of attempting a statement like

Code:

UPDATE DISTINCTROW ....  TableA INNER JOIN TableB ... etc.etc.

When the relationship is one-to-many, SQL is going to update as many rows as there are in the CHILD table, not the PARENT table. These rows, because they are virtual, might have significant overlap so that the virtual fragment coming from the parent gets updated more than once. But because a JOIN's rows are virtual, each combination implied by the JOIN counts as a distinct row if the child has a PK.

Stated another way, when updating JOIN records, the number of affected records is not restricted to the count of the smaller participant.

The_Doc_Man · Jul 31, 2018

From post #10:

Code:

dbMDR.Execute "UPDATE tblSettlements SET PAID = TRUE UPDATE tblPaymentsSub INNER JOIN tblPaymentsSub ON tblSettlements.SettlementID = tblPaymentsSub.SettlementID SET tblSettlements.PAID = tblSettlements.PAID AND tblPaymentsSub.PAID;"

This won't work in this form because you have one Execute and two queries. Syntactically I don't know what SQL would do with that other than barf. What I suggested was correct for "pure" Access situations. You would have to do whatever is the right syntax to (a) make this a pass-thru query from Access OR make the two queries named queries residing on the BE that can be activated from Access as a FE and (b) make the pair act as a transaction using mySQL syntax for begin/commit situations.

Code:

dbMDR.Execute "UPDATE tblSettlements SET PAID = TRUE ;"

dbMDR.Execute "UPDATE tblPaymentsSub INNER JOIN tblPaymentsSub ON tblSettlements.SettlementID = tblPaymentsSub.SettlementID SET tblSettlements.PAID = tblSettlements.PAID AND tblPaymentsSub.PAID;"

Emphasis: My statement (a) above is an either-or situation. Either define the queries by name on the BE and just activate them, OR make them pass-thru from the FE.

Loop and test Child records - update parent (1 Viewer)

DKoehne

Registered User.

pbaldy

Wino Moderator

June7

AWF VIP

isladogs

MVP / VIP

DKoehne

Registered User.

June7

AWF VIP

Mark_

Longboard on the internet

isladogs

MVP / VIP

DKoehne

Registered User.

The_Doc_Man

Immoderate Moderator

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread