Results 1 to 5 of 5

Thread: query help!!

  1. #1
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Unanswered: query help!!

    i need to make an update query which should do like this

    from
    account----date_transaction----amount1----amount2
    1111111----2003/12/03----------100.00-----13.00
    1111111----2003/12/04----------107.00-----15.00
    1111111----2003/12/04----------119.00-----12.00
    2222222----2003/12/01----------203.00-----09.00

    to
    account----date_transaction----amount1----amount2
    1111111----2003/12/03----------113.00-----13.00 <<< (100 + 13 = 113)
    1111111----2003/12/04----------134.00-----15.00 <<< (119 + 15 = 134)
    1111111----2003/12/04----------131.00-----12.00 <<< (119 + 12 = 131)
    2222222----2003/12/01----------212.00-----09.00 <<< (203 + 9 = 212)

    i.e. update field amount1 with

    max(amount1)[for same account number and date_transaction] + amount2 for the same record.

    The solution which I am thinking of is using a cursor. I am sure there must be a simpler way to achieve this. please help me out.

    Thank you
    Rohit
    Last edited by rohitkumar; 12-05-03 at 12:06.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: query help!!

    update mytable
    set amount1 = (select max(amount1) from mytable tt where tt.account = t.account and tt.transaction_date = t.transaction_date) + amount1
    from mytable t

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: query help!!

    CORRECTION,

    update mytable set amount1 = (select max(amount1) from mytable tt where tt.account = t.account and tt.transaction_date = t.transaction_date) + AMOUNT2
    from mytable t

  4. #4
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    i tried it, but am getting error

    here is the query

    update interest_atlas_test
    set amt_principal_collected = (select max(b.amt_principal_collected) from interest_atlas_test b where b.account_schedule_nbr_id = a.account_schedule_nbr_id and b.date_transaction = a.date_transaction) + a.amt_principal_balance
    from interest_atlas_test a
    go

    and the error :
    Invalid column name 'amt_principal_balance'.

    interest_atlas_test table is like:
    account_schedule_nbr_id----date_transaction----amt_principal_collected----amt_principal_balance
    1111111----2003/12/03----------100.00-----13.00
    1111111----2003/12/04----------107.00-----15.00
    1111111----2003/12/04----------119.00-----12.00
    2222222----2003/12/01----------203.00-----09.00

  5. #5
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    Is it possible that the column name was incorrect? Here is my query after I changed min_lvl and max_lvl to INT in Jobs table of Pubs database. It executed without any errors.


    use pubs
    go
    update jobs set min_lvl = (select max(min_lvl) from jobs b where b.job_id = a.job_id) + a.max_lvl
    from jobs a
    go

Posting Permissions

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