title: Update values in a table based on a self-join query to itself in Oracle
body: <p>I have a table that contains logged events for manufactured items.  We consider each event as having 2 statuses that are based on detail and calculations from the prior logged events for the same item.  So, I've developed a SELECT query that uses multiple self-joins to analyze factors from prior events relative to each event, and calculates the statuses.  But because this query is relatively slow, I have added 2 status columns, and I want to UPDATE the columns with the calculated statuses well after the events happen.  This way I can get fast reports later on the status columns instead of having to run all the calculations each time.</p>
  
  <p>Here would be my table:</p>
  
  <pre><code>CREATE TABLE ItemLog
  (
     ItemID decimal(11) NOT NULL,
     MessageTime DATE NOT NULL, 
     Temperature float(7), 
     Voltage float(7),
     Status1 VARCHAR2(10 BYTE), 
     Status2 VARCHAR2(10 BYTE),
     CONSTRAINT "ItemLog_PK" PRIMARY KEY ("ItemID ", "MessageTime ")
  );
  </code></pre>
  
  <p>My SELECT calculation query is something like this:</p>
  
  <pre><code>SELECT ItemID, MessageTime, 
      CASE WHEN A.Voltage&lt;B.Voltage and A.Voltage&lt;C.Avg_Voltage and C.SD_Voltage&lt;5 THEN 'Good' ELSE 'Bad' END Calculated_Status1, 
      CASE WHEN A.Temperature&lt;B.Temperature and A.Temperature&gt;C.Temperature and C.SD_Temperature&gt;10 THEN 'Good' ELSE 'Bad' END Calculated_Status2 
  FROM ItemLog A,
     (SELECT F.ItemID,
          F.MessageTime Key_MessageTime,
          S.Voltage,
          S.Temperature
      FROM ItemLog F,
          ItemLog S
      WHERE F.ItemID=S.ItemID 
          and S.MessageTime=
              SELECT MAX(MessageTime)
              FROM ItemLog
              WHERE ItemID=F.ItemID
                 and MessageTime&lt;F.MessageTime
                 and Voltage&lt;12
                 and Temperature&lt;125
      ) B,  -- Returns the Voltage and Temperature from the prior time it was &lt;12 and &lt;125
     (SELECT K.ItemID, K.MessageTime,
          AVG(L.Temp) Avg_Temperature, STDDEV(L.Temperature) SD_Temp, 
          AVG(L.Voltage) Avg_Voltage, STDDEV(L.Voltage) SD_Voltage 
      FROM ItemLog K,
          ItemLog L
      WHERE K.ItemID=L.ItemID 
          and L.MessageTime=
              SELECT MAX(MessageTime)
              FROM ItemLog
              WHERE ItemID=K.ItemID
                 and MessageTime&lt;K.MessageTime
      GROUP BY K.ItemID, K.MessageTime
       ) C  -- Returns the Voltage and Temperature stats from all prior messages
     (SELECT ItemID 
      FROM ItemLog
      WHERE Voltage&gt;40
       ) D  -- Returns all ItemID where Voltage was ever &gt;40, to exclude them
  WHERE A.ItemID=B.ItemID and A.MessageTime=B.MessageTime
    and A.ItemID=C.ItemID and A.MessageTime=C.MessageTime
    and A.ItemID=D.ItemID(+) and D.ItemID IS NULL
  </code></pre>
  
  <p>So, the question is, how can I update the Status1 and Status2 columns in the table to be the Calculated_Status1 and Calculated Status2 columns?  I've tried taking my calculation query and joining it to the table by the 2 primary keys, but I get the "ORA-01779: cannot modify a column which maps to a non key-preserved table" error.  </p>
  
  <pre><code>UPDATE ( 
      SELECT U.*,
          V.Calculated_Status1
          V.Calculated_Status2
      FROM ItemLog U,
          ( &lt;calculation query above&gt; ) V
      WHERE U.ItemID=V.ItemID and U.MessageTime=V.MessageTime )
  SET U.Status1=V.CalculatedStatus1,
      U.Status2=V.CalculatedStatus2
  </code></pre>
  
  <p>I could imagine an UPDATE with a <code>SET Status1=(SELECT...</code> but that would need some sort of correlated WHERE for the ItemID and MessageTime, and I'd expect it to run horribly slow.  It would seem that there should be a more direct way to do this?</p>