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

Question

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

Do you mean 15 should become 10 for all user 111 rows in table A? — jarlh
– jarlh, Commented Jan 18, 2023 at 15:19
Unfortunately, this is the reality I have to face at work now, I will try to remake the concept, but previous developers made it this way and I need to recover some data — Bogdan Goncharenko
– Bogdan Goncharenko, Commented Jan 18, 2023 at 15:23

Albina · Accepted Answer · 2023-01-18 20:39:47Z

1

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.

answered Jan 18, 2023 at 20:39

Albina

2,0213 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

K_mns · Accepted Answer · 2023-01-18 16:18:46Z

1

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

edited Jan 18, 2023 at 16:18

answered Jan 18, 2023 at 15:14

K_mns

7115 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related