How to order rows by value from another table-CodePudding

There is a table that has an empty column, I need to fill up this column using row_number() and order it by value from other table. My CTE works but I can't update the second table.

This is CTE (it works)

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)

This request to update column but it doesn't work

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 = testy.b
where test_tab.empno = testy.a

CodePudding user response：

If you are on Db2 for LUW, then UPDATE FROM SELECT syntax is supported.
MERGE can be used otherwise.
Note, that your subselect MUST NOT contain duplicates by 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	ID
10	2
20	1

fiddle

CodePudding user response：

you qery shpould gove you an error that indicates that testy.b is unknown

Bu you can do

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)