Hello,

I have a table containing some Account Numbers and I would like to
update a column in that table with the total number of transactions for
each account.

The transactions are in a separate table.

I don't want to use a cursor to do this - can anyone help me with the
Update Statement.

I guess it would be something along the lines of

Update TableAccounts
Set TotalTrans = (Select Count(*) From Transactions)
etc etc

I'm just not sure how to make sure the total number of transactions is
correct for each account number.

Thanks

Barry

You are sure right NOT to use a cursor in here, there are many
solutions for that, assuming that the AccountId is the column mapping
the tables it should be:

Update TableAccounts
Set TotalTrans = Countquery.Counts
        Select Count(*) as Counts,AccountID from TransactionTable Group
by AccountID
) Countquery
On Countquery.AccountID = A.Accountid



Update TableAccounts
Set TotalTrans = (Select Count(*) From Transactions T where A.AccountID
= T.AccountID)
HTH, Jens Suessmeyer.

Hi Jens,

Thank you! That's just what I was looking for.

I had seen that type of Query before in this Group but I just couldn't
find it anywhere!

Many Thanks

Barry

You can accomplish this with a correlated subquery:

Update TableAccounts
Set TotalTrans = (Select Count(*) From Transactions Where
Transactions.ForeignKeyColumnName = TableAccounts.PrimaryKeyColumnName)

But I question the logic of storing the total in TableAccounts.  You will
have to run the above query every time the Transactions table is
modified--probably in a query, otherwise the data in TableAccounts will
become stale.  If there is an overriding performance reason for doing this,
then the correct way to accomplish this is via a trigger on Transactions
that updates TotalTrans anytime a change occurs, and probably a trigger on
TableAccounts that prevents updates to TotalTrans unless they're coming from
the trigger on Transactions (TRIGGER_NESTLEVEL(OBJECT_ID(N'triggerName'))
provides this functionality).


Show quoteHide quote

Selecting when Dates in where clause
"If you want a lot of information, one has to use cursors."
How to get variable number of rows on one row.
Returning a value from query
slow Select query specifying daterange using variables.