Update Column to Another Column from Previous Record in Same Table 1

[PGO01]

I have a table called AddressHistory with PersonID, CaseID, StartDate and EndDate.

StartDate is a new column, and I need go through and update all of the StartDate values to be the EndDate of the previous record that belongs to the same Person.

Can't seem to get there on my own any help would be appreciated.

 

[r937]

to do this with sql, you'll need a self-join, so start by writing the query that produces the correct join

SELECT this.PersonID
     , this.EndDate
     , prev.EndDate
  FROM daTable AS this
LEFT OUTER
  JOIN daTable AS prev
    ON prev.PersonID = this.PersonID
   AND prev.EndDate =
       ( SELECT MAX(EndDate)
           FROM daTable
          WHERE PersonID = this.PersonID
            AND EndDate < this.EndDate )

if this produces the correct pairing of rows (i did not test it) then you have the basis needed for your update



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon