Sql server – nested updates

Topic

New Reply

Hi folks,

Can you help a SQL newbie with nested Update statements?

I have a table – lets call it Table 1, with a PK of a client identification Number (Table1.Cust_Id)

Table 1 Joins to Table 2 against a £Balance field which can show + or – balances (e.g.£10.00 or -£10.00) – there are many values in the £Balance field to the single Cust_Id. I have put CustId and all balances (with other identifying info) into a third Table – Table 3.

I’ve added a couple of fields CR_BAL and DB_Bal to Table 1. What I want to do without creating more tables and updating from them
is a sum of the aggregate Credit balances and Debit Balances from Table3 against the Cust_Id into the CR_Balance/Deb_Balance Fields.

Is there a nested way I can write this without creating and dropping temporary count tables?

thankeeeee!

Blitzy

Reply | Quote

Replies

I’m pretty sure I don’t fully understand the table structure you are trying to use – is Table 3 being derived from data in Tables 1 and 2? In either event, we need to know what database product you are using – is is Access (if so the version would also be helpful), or SQL Server, or mySQL or Oracle or ….?

Reply | Quote

First, a possible solution for the update, if I got your structure right:

Code:

   UPDATE T1 SET T1.CR_BAL = T3.CR_Balance, T1.DB_Bal = T3.Deb_Balance
   FROM Table1 T1 INNER JOIN Table3 T3 ON T1.Cust_Id = T3.Cust_Id

This will work on SQL Server.

Now to the table structure thing. I would not create table T3, it’s unneeded, the balance amounts could be determined through a query.

You also need to be aware that these calculated fields on T1 will have inconsistent values unless they are continously updated. If you have may records on Table2 you may need to have the calculated fields, but I would probably go with updating them automatically, through triggers, to keep the values consistent. If you need to have access to the balance fields only infrequently, maybe calculating them only when you need them could be an answer, but depends on how many records you expect to have on Table2.

Reply | Quote

Thanks, I got it to work! Cheers for help.

Reply | Quote