Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Select then update ???

    I'm trying to avoid using a cursor. Let me describe what i'm looking
    for. It's just an example

    I have two tables.
    The first one : Summary
    Fields : State
    qty1, qty2, up to qty24


    The second one : transactions
    Fields : State
    date
    qty1, qty2, up to qty24

    Users enter their information in transactions. At the end of the day,
    the transactions need to e summarize in summary.

    Then first day is kind of easy. My problem is at the end of the day.

    I want to read each new record in transactions, and add it to the
    existing record in summary.

    I would like to do:

    Select qty1 to qty24 from transactions
    update summary.qty1 = summary.qty1 + transactions.qty1
    qty2 to qty24
    where summary.state = transactions.state.

    I can do it with a cursor but i'm trying to avoid the cursor.

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    update Summary
    set Qty1 = S.Qty1 + T.Qty1,
    Qty2 = S.Qty2 + T.Qty2,
    ...
    Qty24 = S.Qty24 + T.Qty24
    from Summary S inner join transactions T
    on S.date = T.Date ??

Posting Permissions

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