title: H2, how to update with nested selects?
body: <p>I have two tables, <code>EVENT</code> and <code>EVENT_REV</code></p>
  
  <p>EVENT:</p>
  
  <pre><code>╔══════════╦════════════════════╗
  ║ EVENT_ID ║ SENT_INTO_WF_BY_ID ║
  ╠══════════╬════════════════════╣
  ║        1 ║ null               ║
  ║        2 ║ null               ║
  ║        3 ║ null               ║
  ║        4 ║ null               ║
  ║        5 ║ null               ║
  ╚══════════╩════════════════════╝
  </code></pre>
  
  <p>and EVENT_REV:</p>
  
  <pre><code>╔══════════════╦══════════╦═════════╦════════╦════════════╦══════════╗
  ║ EVENT_REV_ID ║ EVENT_ID ║ USER_ID ║ STATUS ║ VALID_FROM ║ VALID_TO ║
  ╠══════════════╬══════════╬═════════╬════════╬════════════╬══════════╣
  ║            1 ║        1 ║      54 ║      0 ║       1000 ║ 1001     ║
  ║            2 ║        1 ║      55 ║    100 ║       2000 ║ 2001     ║
  ║            3 ║        1 ║      56 ║    200 ║       3000 ║ 3001     ║
  ║            4 ║        2 ║      57 ║      0 ║       4000 ║ 4001     ║
  ║            5 ║        3 ║      58 ║      0 ║       5000 ║ 5001     ║
  ║            6 ║        3 ║      59 ║    100 ║       6000 ║ null     ║
  ║            7 ║        4 ║      60 ║      0 ║       7000 ║ null     ║
  ║            8 ║        5 ║      61 ║    500 ║       8000 ║ 8001     ║
  ║            9 ║        5 ║      62 ║    600 ║       9000 ║ 9001     ║
  ╚══════════════╩══════════╩═════════╩════════╩════════════╩══════════╝
  </code></pre>
  
  <p>I want to update the EVENT table and set the <code>SENT_INTO_WF_BY_ID</code>
  The rule for this is:</p>
  
  <ul>
  <li>event_ids should match (EVENT.EVENT_ID = EVENT_REV.EVENT_ID)</li>
  <li>take the row where STATUS is not equal to the STATUS with the lowest VALID_FROM. Which should be the row with the second lowest VALID_FROM</li>
  <li>From that row, take the USER_ID</li>
  </ul>
  
  <p>For example:
  For the EVENT_ID = 1 it should select the 2nd row from EVENT_REV and put the USER_ID 55 into the SENT_INTO_WF_BY_ID</p>
  
  <p>Because inner joins are not allowed for H2, my query looks like this:</p>
  
  <pre><code>UPDATE event ltm
  SET ltm.sent_into_wf_by_id =
    (SELECT top 1 ltmRev.user_id
     FROM event_rev ltmRev
     WHERE ltmRev.event_id = ltm.event_id
       AND ltmRev.status !=
         (SELECT top 1 EVENT_REV.status
          FROM EVENT_REV
          ORDER BY valid_from ASC nulls LAST)
     ORDER BY ltmRev.valid_to ASC nulls LAST)
  </code></pre>
  
  <p>The result should look like:</p>
  
  <pre><code>╔══════════╦════════════════════╗
  ║ EVENT_ID ║ SENT_INTO_WF_BY_ID ║
  ╠══════════╬════════════════════╣
  ║        1 ║ 55                 ║
  ║        2 ║ null               ║
  ║        3 ║ 59                 ║
  ║        4 ║ null               ║
  ║        5 ║ 62                 ║
  ╚══════════╩════════════════════╝
  </code></pre>
  
  <p>but it's actually:</p>
  
  <pre><code>╔══════════╦════════════════════╗
  ║ EVENT_ID ║ SENT_INTO_WF_BY_ID ║
  ╠══════════╬════════════════════╣
  ║        1 ║ 55                 ║
  ║        2 ║ null               ║
  ║        3 ║ 59                 ║
  ║        4 ║ null               ║
  ║        5 ║ 61 &lt;-- wrong       ║
  ╚══════════╩════════════════════╝
  </code></pre>