Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_a (ID, Age, Dept, Salary, Start_date) AS
SELECT 1, 30, 'A', 1000, DATE '2000-01-01' FROM DUAL UNION ALL
SELECT 1, 31, 'B', 1200, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 2, 25, 'C', 1200, DATE '2021-06-01' FROM DUAL UNION ALL
SELECT 2, 26, 'A', 1300, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 3, 34, 'D', 1400, DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 3, 35, 'C', 1800, DATE '2022-01-01' FROM DUAL;
6 rows affected

CREATE TABLE Table_B (ID, Salary, Start_date) AS
SELECT 1, 1500, DATE '2022-06-01' FROM DUAL UNION ALL
SELECT 2, 1800, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 3, 1600, DATE '2021-06-01' FROM DUAL
3 rows affected

SELECT id,
       LAST_VALUE(age) IGNORE NULLS
         OVER (PARTITION BY id ORDER BY start_date) AS age,
       LAST_VALUE(dept) IGNORE NULLS
         OVER (PARTITION BY id ORDER BY start_date) AS dept,
       salary,
       start_date
FROM   (
  SELECT id, age, dept, salary, start_date
  FROM   table_a
UNION ALL
  SELECT id, NULL, NULL, salary, start_date
  FROM   table_b
)


ID
AGE
DEPT
SALARY
START_DATE


1
30
A
1000
2000-01-01 00:00:00

1
31
B
1200
2022-01-01 00:00:00

1
31
B
1500
2022-06-01 00:00:00

2
25
C
1200
2021-06-01 00:00:00

2
26
A
1300
2022-01-01 00:00:00

2
26
A
1800
2022-01-01 00:00:00

3
34
D
1400
2021-01-01 00:00:00

3
34
D
1600
2021-06-01 00:00:00

3
35
C
1800
2022-01-01 00:00:00


INSERT INTO table_a (id, age, dept, salary, start_date)
SELECT b.id, a.age, a.dept, b.salary, b.start_date
FROM   table_b b
       LEFT OUTER JOIN LATERAL (
         SELECT a.age, a.dept
         FROM   table_a a
         WHERE  a.id = b.id
         AND    a.start_date <= b.start_date
         ORDER BY start_date DESC
         FETCH FIRST ROW ONLY
       ) a
       ON 1 = 1;
3 rows affected

SELECT *
FROM   table_a
ORDER BY id, start_date


ID
AGE
DEPT
SALARY
START_DATE


1
30
A
1000
2000-01-01 00:00:00

1
31
B
1200
2022-01-01 00:00:00

1
31
B
1500
2022-06-01 00:00:00

2
25
C
1200
2021-06-01 00:00:00

2
26
A
1300
2022-01-01 00:00:00

2
26
A
1800
2022-01-01 00:00:00

3
34
D
1400
2021-01-01 00:00:00

3
34
D
1600
2021-06-01 00:00:00

3
35
C
1800
2022-01-01 00:00:00

ID	AGE	DEPT	SALARY	START_DATE
1	30	A	1000	2000-01-01 00:00:00
1	31	B	1200	2022-01-01 00:00:00
1	31	B	1500	2022-06-01 00:00:00
2	25	C	1200	2021-06-01 00:00:00
2	26	A	1300	2022-01-01 00:00:00
2	26	A	1800	2022-01-01 00:00:00
3	34	D	1400	2021-01-01 00:00:00
3	34	D	1600	2021-06-01 00:00:00
3	35	C	1800	2022-01-01 00:00:00