sql - H2，如何使用嵌套选择进行更新？

Question

我有两个表，EVENT 和 EVENT_REV

事件:

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

和 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     ║
╚══════════════╩══════════╩═════════╩════════╩════════════╩══════════╝

我想更新 EVENT 表并设置 SENT_INTO_WF_BY_ID规则是:

• event_ids 应该匹配 (EVENT.EVENT_ID = EVENT_REV.EVENT_ID)
• 取 STATUS 不等于具有最低 VALID_FROM 的 STATUS 的行。哪一行应该是第二低的 VALID_FROM
• 从该行中获取 USER_ID

例如:对于 EVENT_ID = 1，它应该从 EVENT_REV 选择第二行并将 USER_ID 55 放入 SENT_INTO_WF_BY_ID

因为 H2 不允许内部联接，所以我的查询如下所示:

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)

结果应该是这样的:

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

但实际上是:

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

Answer 1

可以用下面的查询来解决:

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;

缺少的部分是最内层选择中的 AND ltmRev.ltm_op_risk_event_id = ltmRev2.ltm_op_risk_event_id。我首先用错误的连接测试了这个连接......