title: Interpolate missing values in a MySQL table
body: <p>I have some intraday stock data saved into a MySQL table which looks like this:</p>
  
  <pre><code>+----------+-------+
  | tick     | quote |
  +----------+-------+
  | 08:00:10 |  5778 |
  | 08:00:11 |  5776 |
  | 08:00:12 |  5778 |
  | 08:00:13 |  5778 |
  | 08:00:14 |  NULL |
  | 08:00:15 |  NULL |
  | 08:00:16 |  5779 |
  | 08:00:17 |  5778 |
  | 08:00:18 |  5780 |
  | 08:00:19 |  NULL |
  | 08:00:20 |  5781 |
  | 08:00:21 |  5779 |
  | 08:00:22 |  5779 |
  | 08:00:23 |  5779 |
  | 08:00:24 |  5778 |
  | 08:00:25 |  5779 |
  | 08:00:26 |  5777 |
  | 08:00:27 |  NULL |
  | 08:00:28 |  NULL |
  | 08:00:29 |  5776 |
  +----------+-------+
  </code></pre>
  
  <p>As you can see, there are some points where no data is available (quote is <code>NULL</code>). What I would like to do is a simple step interpolation. This means each <code>NULL</code> value should be updated with the last value available. The only way I managed to do this is with cursors, which is pretty slow due to the large amount of data. I'm basically searching something like this:</p>
  
  <pre><code>UPDATE table AS t1
  SET quote = (SELECT quote FROM table AS t2 
               WHERE t2.tick &lt; t1.tick AND 
                     t2.quote IS NOT NULL 
               ORDER BY t2.tick DESC 
               LIMIT 1)
  WHERE quote IS NULL
  </code></pre>
  
  <p>Of course this query will not work, but this is how it should look like.</p>
  
  <p>I would appreciate any ideas on how this can be solved without cursors and temp tables.</p>