如果您在Db2上执行LUW，则支持UPDATE FROM SELECT语法。
MERGE也可用于其它情况。
请注意，您的子选择不能包含EMPNO的重复项。

CREATE TABLE TEST_TAB (EMPNO INT, ID INT);
INSERT INTO TEST_TAB (EMPNO) VALUES 10, 20;

CREATE TABLE EMPLOYEE (EMPNO INT, BIRTHDATE DATE);
INSERT INTO EMPLOYEE (EMPNO, BIRTHDATE)
VALUES
  (10, '1970-01-01'::DATE)
, (20, '1980-01-01'::DATE)

UPDATE TEST_TAB T
SET ID = E. order_by_id  
FROM 
(
select 
  t1.empno
, ROW_NUMBER() OVER (ORDER BY t2.birthdate DESC) as order_by_id  
from test_tab as t1 
join employee as t2 on t2.empno = t1.empno
) E
WHERE T.EMPNO = E.EMPNO;

SELECT * FROM TEST_TAB;

| EMPNO公司|识别码|
| - -|- -|
| 10个|2个|
| 二十个|一个|
fiddle

您的查询应该会得到一个错误，指出testy.B是未知的
但是你可以做

with testy(a, b, c) as (select 
                           t1.empno
                           , t2.birthdate
                           , ROW_NUMBER() OVER(ORDER BY t2.birthdate DESC) as order_by_id  
                       from test_tab as t1 join employee as t2 on t2.empno = t1.empno)
update test_tab
set test_tab.id = (SELECT testy.b FROM testy where test_tab.empno = testy.a)