Results 1 to 3 of 3

Thread: update query

  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: update query

    I've been asked to write an update query and I'm not sure where to start. In our database a client record can have many addresses in the address table and the basic task is to update the client's address records so that only one is "active". For each client, if there is only one address, don't touch the address. If there are multiple addresses, don't touch the address record with the newest "start date", and set the "end date" for each of the older address records. The trick is that the "end date" should be equal to the "start date" of the next address record for that client. If an "end date" is already present, then it should remain untouched.

    Below are examples of the expected results. Any help would be much appreciated.

    Original Tables:
    Client Address
    ID Start Date End Date
    100 2/1/2009
    100 5/10/2010
    100 10/1/2010
    200 7/1/2010
    200 8/25/2010
    300 3/15/2009
    400 5/22/2010 7/12/2010
    400 7/12/2010


    New Tables:
    Client Address
    ID Start Date End Date
    100 2/1/2009 5/10/2010
    100 5/10/2010 10/1/2010
    100 10/1/2010
    200 7/1/2010 8/25/2010
    200 8/25/2010
    300 3/15/2009
    400 5/22/2010 7/12/2010
    400 7/12/2010

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you would want something like:

    Code:
    update table x
    set end_date = (select min(t.start_date)
                             from table t
                           where t.id = x.id
                               and t.start_date > x.start_date)
    where x.end_date is null
      and 1 < (select count(*)
                      from table c
                   where x.id = c.id)
    Dave Nance

  3. #3
    Join Date
    Nov 2010
    Posts
    4
    Thanks for the help. I had to tweak the code some, but you got me on the right track.

Posting Permissions

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