Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40

    Question Unanswered: Update records with first found data

    I am trying to combine 2 tables, one is an event table, the other is a system table.

    I would like to update field system.transaction with the first event.transaction

    Where event.account = system.account
    and event.effdt > system.effdt
    and event.effdt <= system.effdt + 30

    It's not really a join so I'm not sure how to write it.

    Thanks,
    Doug
    Doug

  2. #2
    Join Date
    Feb 2004
    Posts
    78
    Well, assuming that event.transaction is numeric, so a lesser number is first, how about something like...

    update system
    set transaction = (select min(transaction) transaction from event where account=system.account and effdt > system.effdt and event.effdt <=system.effdt+30)
    where transaction is null -- (don't overwrite previous?)
    and exists (select min(transaction) from event where account=system.account and effdt > system.effdt and event.effdt <=system.effdt+30) -- this exists may not be needed

    or if event.transaction is a guid of some sort and can't be used for sorting, go be date:

    update system
    set transaction = (select top 1 transaction from event where account=system.account and effdt > system.effdt and event.effdt <=system.effdt+30 order by effdt asc)
    where transaction is null -- (don't overwrite previous?)
    and exists select top 1 transaction from event where account=system.account and effdt > system.effdt and event.effdt <=system.effdt+30 order by effdt asc) -- this exists may not be needed

    Hope this helps,
    Eric

  3. #3
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40
    That's it! Thanks for the help!!!
    Doug

Posting Permissions

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