Update last row with data from second to last row

Question

CREATE TABLE Landmark
(
   Indexc NUMBER
   Birth DATE
   Address VARCHAR2(50 BYTE)
   City VARCHAR2(30 BYTE)
   State CHAR(2 BYTE)
   Zip VARCHAR2(15 BYTE)
   ...
   Other properties
)  

ALTER TABLE Landmark ADD (
   CONSTRAINT Landmark_PK PRIMARY KEY (Indexc, Birth));  

CREATE TABLE MapPoint
(
   Indexc NUMBER
   Longitude FLOAT(126)
   Latitude FLOAT(126)
   BuildDate DATE
)  

ALTER TABLE MapPoint ADD (
   CONSTRAINT MapPoint_FK FOREIGN KEY (Indexc) REFERENCES Landmark (Indexc));

The Landmark table holds a list of landmarks that can move from time to time. The MapPoint table holds the longitude and latitude for those landmarks. Any time the properties for a landmark changes, a new row is inserted into the Landmark table for that landmark with a new birth date sans the address.

A script I have will set the BuildDate to null in the MapPoint table for all the landmarks that haven't physically moved and I am trying to write an UDPATE statement that will reuse the address from the last row. The following is close to what I want, but doesn't work becuase the inner most sub-query won't correlate with the update statement. I get ORA-00904: "lm2"."Index": invalid identifier.

UPDATE Landmark lm1
   SET (Address, City, State, Zip) = (
   SELECT Address, City, State, Zip
     FROM Landmark lm2
    WHERE lm2.Indexc = lm1.Indexc
      AND lm2.birth = (
      SELECT MIN(Birth)
        FROM (
         SELECT Birth
           FROM Landmark lm3
          WHERE lm3.Indexc = lm2.Indexc
          ORDER BY Birth DESC)
         AND ROWNUM < 3))
 WHERE Indexc IN (
   SELECT Indexc
     FROM MapPoint
    WHERE BuildDate IS NULL);

Sample Before:

Indexc                    Birth        Address          City   State     Zip
------   ----------------------   ------------   -----------   -----   -----
    45    8/16/2009  4:46:21 AM    123 Main St   Springfield      PA   84679
    45    8/18/2009  7:20:27 PM    123 Main St   Springfield      PA   84679
    45    8/20/2009  9:01:44 PM   456 Smith Ln   Springfield      PA   84153
    45   10/31/2009 12:29:07 AM

Sample After:

Indexc                    Birth        Address          City   State     Zip
------   ----------------------   ------------   -----------   -----   -----
    45    8/16/2009  4:46:21 AM    123 Main St   Springfield      PA   84679
    45    8/18/2009  7:20:27 PM    123 Main St   Springfield      PA   84679
    45    8/20/2009  9:01:44 PM   456 Smith Ln   Springfield      PA   84153
    45   10/31/2009 12:29:07 AM   456 Smith Ln   Springfield      PA   84153

Answer 1

I replaced "index" with "indexc" because index is not a valid column name in oracle. I think this does what you want though:

UPDATE Landmark lm1
 SET (Address, City, State, Zip) = (
 SELECT Address, City, State, Zip
   FROM Landmark lm2
  WHERE lm2.Indexc = lm1.Indexc
    AND lm2.birth = (
    SELECT MAX(Birth)
      FROM Landmark lm3
        WHERE lm3.Indexc = lm2.Indexc
        and lm3.birth < lm2.birth))
 WHERE  (indexc,birth) in (
         select indexc, max(birth)
         from   Landmark
         where  Indexc IN (
           SELECT Indexc
           FROM   MapPoint
           WHERE  BuildDate IS NULL));

Answer 2

This works perfectly. Although its not very extensible for nth newest item.

UPDATE Landmark lm1
   SET (Address, City, State, Zip) = (
   SELECT Address, City, State, Zip
     FROM Landmark lm2
    WHERE lm2.Indexc = lm1.Indexc
      AND lm2.birth = (
      SELECT MAX(Birth)
        FROM Landmark lm3
       WHERE lm3.Indexc = lm2.Indexc
         AND lm3.birth < (
         SELECT MAX(Birth)
           FROM Landmark lm4
          WHERE lm4.Indexc = lm3.Indexc)))
 WHERE Indexc IN (
   SELECT Indexc
     FROM MapPoint
    WHERE BuildDate IS NULL)
   AND Birth = (
   SELECT MAX(Birth)
     FROM Landmark lm2
    WHERE lm2.Indexc = lm1.Indexc);

Answer 3

This solution uses an analytic function. The code looks slightly more redundant but the cost is lower, at least in my test database, probably because there are fewer subqueries:

update landmark L1 set (address, city, state, zip) =  
    (select address, city, state, zip from 
       (select  last_value(address ignore nulls) over (partition by cindex order by birth) address, 
                last_value(city ignore nulls) over (partition by cindex order by birth) city, 
                last_value(state ignore nulls) over (partition by cindex order by birth) state, 
                last_value(zip ignore nulls) over (partition by cindex order by birth) zip, 
                cindex, birth
          from landmark) L2
      where l1.cindex = l2.cindex and l2.birth = l1.birth)       
where CIndex IN (
 SELECT CIndex
     FROM MapPoint
    WHERE BuildDate IS NULL)
    and address is null

Basically it's saying, fetch all the rows from the table and for each row, select the address values if they're not null or the last non-null value if they are, then update those values for the rows that need to be updated.