Question 90075 in PostgreSQL Question posted in
The official documentation can be found here.

How to replace multiple fields values with values from another table? – Postgresql

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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search