H2, how to update with nested selects?

Question

I have two tables, EVENT and EVENT_REV

EVENT:

╔══════════╦════════════════════╗
║ EVENT_ID ║ SENT_INTO_WF_BY_ID ║
╠══════════╬════════════════════╣
║        1 ║ null               ║
║        2 ║ null               ║
║        3 ║ null               ║
║        4 ║ null               ║
║        5 ║ null               ║
╚══════════╩════════════════════╝

and EVENT_REV:

╔══════════════╦══════════╦═════════╦════════╦════════════╦══════════╗
║ 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     ║
╚══════════════╩══════════╩═════════╩════════╩════════════╩══════════╝

I want to update the EVENT table and set the SENT_INTO_WF_BY_ID The rule for this is:

• event_ids should match (EVENT.EVENT_ID = EVENT_REV.EVENT_ID)
• 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
• From that row, take the USER_ID

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

Because inner joins are not allowed for H2, my query looks like this:

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)

The result should look like:

╔══════════╦════════════════════╗
║ EVENT_ID ║ SENT_INTO_WF_BY_ID ║
╠══════════╬════════════════════╣
║        1 ║ 55                 ║
║        2 ║ null               ║
║        3 ║ 59                 ║
║        4 ║ null               ║
║        5 ║ 62                 ║
╚══════════╩════════════════════╝

but it's actually:

╔══════════╦════════════════════╗
║ EVENT_ID ║ SENT_INTO_WF_BY_ID ║
╠══════════╬════════════════════╣
║        1 ║ 55                 ║
║        2 ║ null               ║
║        3 ║ 59                 ║
║        4 ║ null               ║
║        5 ║ 61 <-- wrong       ║
╚══════════╩════════════════════╝

Answer 1

Could solve it with the following query:

UPDATE ltm_op_risk_event ltm
SET ltm.sent_into_wf_by_id =
  (SELECT ltmRev.adm_user_id
   FROM ltm_op_risk_event_rev ltmRev
   WHERE ltmRev.ltm_op_risk_event_id = ltm.ltm_op_risk_event_id
     AND ltmRev.status !=
       (SELECT ltmRev2.status
        FROM LTM_OP_RISK_EVENT_REV ltmRev2
        WHERE valid_from IS NOT NULL
          AND ltmRev.ltm_op_risk_event_id = ltmRev2.ltm_op_risk_event_id
        ORDER BY valid_from ASC LIMIT 1)
   ORDER BY ltmRev.valid_to ASC LIMIT 1)
WHERE ltm.sent_into_wf_by_id IS NULL;

The missing part was the AND ltmRev.ltm_op_risk_event_id = ltmRev2.ltm_op_risk_event_id in the innermost select. I first tested this connection with the wrong connections...