您需要使用两个查询。第一个用于插入记录，第二个用于更新日期，如下所示：
创建示例数据

SQL> CREATE TABLE yourTable AS (
  2      SELECT '1234' AS Prod_Id, date '2020-03-10' AS Begin_Version_dt, date '2020-04-09' AS End_version_dt FROM dual UNION ALL
  3      SELECT '1234', date '2020-04-10', date '2020-05-10' FROM dual UNION ALL
  4      SELECT '1234', date '2020-05-11', date '9999-12-31' FROM dual
  5  );

Table created.

SQL>
SQL> CREATE TABLE prod_table as
  2  (SELECT 1234 AS PROD_ID, DATE '2020-05-15' AS TRANS_DATE FROM DUAL);

Table created.

当前数据视图：

SQL> SELECT * FROM YOURTABLE ORDER BY BEGIN_VERSION_DT;

PROD BEGIN_VER END_VERSI
---- --------- ---------
1234 10-MAR-20 09-APR-20
1234 10-APR-20 10-MAY-20
1234 11-MAY-20 31-DEC-99

SQL> select * from prod_table;

   PROD_ID TRANS_DAT
---------- ---------
      1234 15-MAY-20

您正在查找的查询

SQL> INSERT INTO yourTable Y
  2  SELECT PROD_ID, TRANS_DATE, TRANS_DATE FROM PROD_TABLE;

1 row created.

SQL>
SQL>
SQL> UPDATE YOURTABLE YY
  2     SET END_VERSION_DT = COALESCE(
  3  (SELECT LD_BEGIN_DT
  4    FROM (SELECT Y.BEGIN_VERSION_DT,
  5     Y.PROD_ID,
  6     LEAD(Y.BEGIN_VERSION_DT)
  7  OVER (PARTITION BY Y.PROD_ID
  8  ORDER BY Y.BEGIN_VERSION_DT) - 1 AS LD_BEGIN_DT
  9    FROM YOURTABLE Y) Y
 10    WHERE YY.PROD_ID = Y.PROD_ID
 11  AND YY.BEGIN_VERSION_DT = Y.BEGIN_VERSION_DT),
 12  DATE '9999-12-31');

4 rows updated.

更新数据：

SQL> SELECT * FROM YOURTABLE ORDER BY BEGIN_VERSION_DT;
PROD BEGIN_VER END_VERSI
---- --------- ---------
1234 10-MAR-20 09-APR-20
1234 10-APR-20 10-MAY-20
1234 11-MAY-20 14-MAY-20
1234 15-MAY-20 31-DEC-99
SQL>

你可以试着用 LEAD 使用默认值：

SELECT
    Prod_Id,
    Begin_Version_dt,
    COALESCE(End_Version_dt,
             LEAD(Begin_Version_dt, 1, date '9999-12-31')
             OVER (PARTITION BY Prod_Id ORDER BY Begin_Version_dt)) AS End_Version_dt
FROM yourTable
ORDER BY
    Prod_Id,
    Begin_Version_dt;

演示

这里的逻辑是，如果可用，我们选择任何非 NULL 结束版本日期。如果结束版本日期不可用，我们将在序列中的下一个开始日期之前开始。如果这也不可用，那么我们默认为报告 9999-12-31 作为结束版本日期。