BorlandTalk.com

Laurie McIntosh · Guest

Howdy people,

Stuck. I've got two tables. I want to update Table B with a count of
related fields in Table A.

So: I can

SELECT DISTINCT D.RepDate, D.AMPM, D.Route, COUNT(DISTINCT
D1.ManifestNo) AS NoSailings
FROM `daterep` D, mfest` D1
WHERE
(D.AMPM = 1.0)
AND (D1.SailDate = D.RepDate)
AND (D1.SailTime < 1200.0)
AND (D1.Route = D.Route)
GROUP BY D.RepDate, D.AMPM, D.Route

[mfest is Table A and repdate is Table B - these are tables in a MYSQL
database. Delphi7 Enterprise, by the way.]

And this works fine. I've got this code in a TADOQuery on a form and I
open it. I know it works fine, because I can see it in a DBGrid. Now.
The four fields I'm returning exist in Repdate. I want to update the
NoSailings field in Repdate with the returned.

What's the best way to do this? I COULD while-step through the TADOQuery
dataset and record by record locate and update them, but surely there's
another way. I'd like to fire an update query off this, but can't work
out how to nest the select and update... or, maybe I can do another
query using the first query as a source... or...?

What's the canonical way to do an update using a calculated select as a
source?

Regards,

---=Laurie

Laurie McIntosh · Guest

Brian Bushay TeamB wrote: