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 contains a list of landmarks that can move from time to time. The MapPoint table contains the longitude and latitude for these landmarks. Each time the properties for a character change, a new row is inserted into the Landmark table for that landmark with a new date of birth without an address.

A script BuildDate null MapPoint , , UDPATE, . , , , . ORA-00904: "lm2". "": .

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);

:

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

:

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

. n- .

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 2

. , , , , , :

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

, , , , , , , .

Answer 3

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

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));