How to replace multiple fields values with values from another table?-CodePudding

I have two tables: TableA

Id Status User
1   15    111
2   15    111
3   15    111

And TableB which contains history of status changes from previous table:

Id IdA Status   Date
1   1   10   2023-01-18
2   1   30   2022-12-18
3   3   30   2022-01-17
4   3   10   2022-01-16

What I need to do is to update status field values for every row with User 111 in TableA with values from TableB, I need to find the latest entity change in TableB and write its status to the corresponding entity in TableA.

So the final result for the TableA should be:

Id Status User
1    10   111
2    15   111
3    30   111

CodePudding user response：

Rank table b based on IdA ordered by date ascending then choose row number 1 to update table a.

Row_number()over(order by ...)

tableb is the second table and the tablen is the 1 table

update tablen t1 set status = t2.status
from 
(select id, IDA, status from (
select *,
row_number ()over(partition by IDA order by daten desc) rnk
from tableb) x 
  where rnk = 1)t2 where t1.id = t2.idA;

this clause is to get the last update

select id, IDA, status from (
select *,
row_number ()over(partition by IDA order by daten desc) rnk
from tableb) x 
  where rnk = 1

CodePudding user response：

update tableA
set status = latest_data.status
from (select distinct on (idA)
       idA, status, date
      from   tableB
      -- helps to retrieve the latest update for each unique idA
      order by idA, date desc) latest_data
where latest_data.idA = tableA.id;

See the docs for DISTINCT ON clause.

See the demo with all the details.