Please help with dynamic update using joints

[avu]

Hello,

Can someone please help me with the following script?

SELECT DISTINCT RegNumber FROM tblPools WHERE (NOT (RegNumber IS NULL))

FOR EACH RegNumber
SELECT poolNumber, COUNT(DISTINCT players) AS numPlayers
FROM R_#RegNumber#
GROUP BY poolNumber
HAVING (poolNumber IN (SELECT DISTINCT poolNumber FROM R_#RegNumber#))
ORDER BY poolNumber

FOR EACH poolNumber
UPDATE tblPools SET TotalPlayers = R_#RegNumber#.numPlayers WHERE (poolNum = R_#RegNumber#.poolNumber)



Table R_#RegNumber#: #RegNumber# corresponds to the RegNumber retrieved from tblPools at top query.

How can I use joints to accomplish this script? I think I could use cursors for looping "FOR EACH RegNumber", and update in "FOR EACH poolNumber". But I think using joint for this script will save lots of server's load.

Thanks

 

[Catadmin]

FOR EACH isn't a valid T-SQL command that I am aware of. Can you explain (in English) what you are trying to accomplish with each segment of code?

Thanks,



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[avu]

My apology. I mixed part script part pseudo code, hence came the above code. By FOR EACH (pseudo), I meant to use cursors to loop through the query above it.

Allow me to provide more info & clean up the above code a bit:
Table structures:

- tblPools
. RegNumber
. subPool
. TotalPlayers

- R_#RegNumber#
. subPool
. subPool_numPlayers

CODE:

SELECT DISTINCT RegNumber FROM tblPools WHERE (NOT (RegNumber IS NULL))

FOR EACH RegNumber
SELECT DISTINCT subPool, COUNT(subPool_numPlayers) AS numPlayers
FROM R_#RegNumber#
GROUP BY subPool

FOR EACH subPool
UPDATE tblPools SET TotalPlayers = R_#RegNumber#.COUNT(subPool_numPlayers) WHERE (subPool = R_#RegNumber#.subPool)


What I'm trying to accomplish is:
- query tblPools for a list of unique RegNumbers
- each RegNumber has a corresponded table R_#RegNumber#
- each R_#RegNumber# contains multiple entries of subPools, and a number of players for each entry (ie. subPool and subPool_numPlayers)
- do a total count of subPool_numPlayers for each subPool, and update tblPools

Hope it clears the air a little.

Thanks for your help.