Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sql server - nested updates

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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 ....?
    Wendell

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  4. #4
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I got it to work! Cheers for help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •