Subquery in UPDATE SET = expression 1

[dilettante]

Can this be done? I seem to have the syntax balled up.

[tt]UPDATE Parent
SET ChildCount = (SELECT Count(1) WHERE Child.ParentID = Parent.ParentID);[/tt]

Yes, the Parent.ChildCount isn't normalized. That's intentional. There is a one-to-many relationship linking the two tables on ParentID though.

The error I get is the familiar one about needing an updateable query. I assume aggregate queries are never updateable.

Is there anything better than the hack I'm using to get around this right now? I'm looping over the Parent table's rows, and for each one doing another query to retrieve the Count(1) of matching Child table rows, updating via ADO.

 

[dilettante]

I should add that:

[tt]UPDATE Parent P
SET ChildCount = (SELECT Count(1) WHERE Child.ParentID = P.ParentID);[/tt]

... did not help matters, though I had hopes for it.

 

[PHV]

UPDATE Parent
SET ChildCount = DCount("*", "Child", "Child.ParentID=" & Parent.ParentID);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dilettante]

*sigh*

One more time (typos):

[tt]UPDATE Parent P
SET ChildCount = (SELECT Count(1) FROM Child WHERE Child.ParentID = P.ParentID);[/tt]

All butterfingers here today.

 

[dilettante]

Thanks for answering this in thread181-986302.

Sometimes you wish you could delete/move/edit posts here.

 

[PHV]

Another way.
1) Create a maketable query like this:
SELECT ParentID, Count(*) AS CountOfID INTO tmpChildStat
FROM Child
GROUP BY ParentID;
2) Then the update query may be something like this:
UPDATE Parent P INNER JOIN tmpChildStat C ON P.ParentID = C.ParentID
SET P.ChildCount = C.CountOfID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244