title: MySQL - update row with difference in column between current and a previous row
body: <p>I have a table data_temp with the following structure:</p>
  
  <pre><code>CREATE TABLE `data_temp` (
    `id` bigint(20) NOT NULL,
    `deviceid` int(11) NOT NULL,
    `registeraddress` int(11) NOT NULL,
    `value` decimal(20,9) NOT NULL,
    `time` datetime NOT NULL,
    `value_diff` decimal(20,9) NOT NULL DEFAULT '0.000000000',
    `time_diff` bigint(20) NOT NULL DEFAULT '0'
  )
  </code></pre>
  
  <p>id is unique but not contiguous.<br>
  Records are unique for deviceid/registeraddress/time.<br>
  Records are inserted which contain values for id,deviceid,registeraddress,value and time.<br>
  I want to calculate value_diff and time_diff which are the difference in value between the inserted record and the previous record corresponding to deviceid and registeraddress and difference in time in seconds.</p>
  
  <p>The table already has a lot of records all without the value_diff and time_diff calculated so I want to update the existing data in the table with these values.</p>
  
  <p>Here is some sample data:</p>
  
  <pre><code>id,  deviceid, registeraddress, value,time    
  11102116190949258, 101, 856, 34.000000000, 2011-10-21 16:19:00
  11102116240432553, 101, 856, 38.000000000, 2011-10-21 16:24:00
  11102116290249672, 101, 856, 44.000000000, 2011-10-21 16:29:00
  11102116340121243, 101, 856, 54.000000000, 2011-10-21 16:34:00
  11102116390025291, 101, 856, 57.000000000, 2011-10-21 16:39:00
  11102116440032519, 101, 856, 63.000000000, 2011-10-21 16:44:00
  </code></pre>
  
  <p>I can do the following select on the table which will list the data that I want:</p>
  
  <pre><code>SELECT 
      r_B.deviceid, 
      r_B.registeraddress,
      r_B.value,
      r_B.time, 
      COALESCE(TIME_TO_SEC(TIMEDIFF(r_B.time,r_A.time)), 0) AS time_diff,
      COALESCE(r_B.value-r_A.value, 0) AS value_diff
  FROM data_temp r_B
      LEFT OUTER JOIN data_temp r_A
      ON r_B.deviceid = r_A.deviceid 
          AND r_B.registeraddress = r_A.registeraddress 
          AND r_B.time &gt; r_A.time
  WHERE NOT EXISTS 
      (
          SELECT r_C.time 
          FROM data_temp r_C
          WHERE r_C.deviceid = r_C.deviceid 
              AND r_C.registeraddress = r_C.registeraddress 
              AND r_C.time &gt; r_A.time 
              AND r_C.time &lt; r_B.time
      )
  ORDER BY r_B.deviceid, r_B.registeraddress, r_B.time
  </code></pre>
  
  <p>result:</p>
  
  <pre><code>id,  deviceid, registeraddress, value, time, value_diff, time_diff
  11102116190949258, 101, 856, 34.000000000, 2011-10-21 16:19:00, 0,   0.000000000
  11102116240432553, 101, 856, 38.000000000, 2011-10-21 16:24:00, 300, 4.000000000
  11102116290249672, 101, 856, 44.000000000, 2011-10-21 16:29:00, 300, 6.000000000
  11102116340121243, 101, 856, 54.000000000, 2011-10-21 16:34:00, 300, 10.000000000
  11102116390025291, 101, 856, 57.000000000, 2011-10-21 16:39:00, 300, 3.000000000
  11102116440032519, 101, 856, 63.000000000, 2011-10-21 16:44:00, 300, 6.000000000
  </code></pre>
  
  <p>I have been trying for a couple of days now to work out how to write the corresponding update. I have tried the following:</p>
  
  <pre><code>UPDATE data_temp r_B
      LEFT OUTER JOIN data_temp r_A
      ON r_B.deviceid = r_A.deviceid 
          AND r_B.registeraddress=r_A.registeraddress 
          AND r_B.time &gt; r_A.time
  SET r_B.time_diff = COALESCE(TIME_TO_SEC(TIMEDIFF(r_B.time,r_A.time)), 0),
      r_B.value_diff = COALESCE(r_B.value-r_A.value, 0)
  WHERE NOT EXISTS 
      (
          SELECT r_C.time 
          FROM data_temp r_C 
          WHERE r_C.deviceid = r_B.deviceid 
              AND r_C.registeraddress = r_B.registeraddress 
              AND r_C.time &gt; r_A.time 
              AND r_C.time &lt; r_B.time
      )
  </code></pre>
  
  <p>But of course get the slightly frustrating MySQL error:</p>
  
  <pre><code>Error Code: 1093
  You can't specify target table 'r_B' for update in FROM clause
  </code></pre>
  
  <p>The problem here is with the WHERE clause at the end. The update works without the clause but gives the wrong result (all records are updated with the difference between the current and the oldest record not the previous record). Everything else in the statement works except the WHERE clause.</p>
  
  <p>I have been researching this for quite a while now and haven't found a good solution so any help would be appreciated. </p>