MySQL Update Statement from joined table (limited by latest value of that table)

Question

I'm using MariaDB 5.5

I have 2 tables, I want to update a column in table A based on the information on table B.

Table B has multiple entries for the ID that I'm looking for but each registry has an updated_at column so I just want to get the latest registry.

select po_number, sce_status
from infor_order
where po_number = @po
order by updated_at desc;

This would result in the following dataset in which I'm only interested on the 'Part Allocated'

So what I want to do is update a column in table A searching the latest value of Table B by the "po_number" but when I try just to do the select to test the join I get the 2 values of table B for each registry

select b.id, b.PO_NUMBER, b.INFOR_SCE_STATUS, infor.sce_status
from planning_backloguov b
left join (
    select distinct po_number, sce_status
    from infor_order
    order by updated_at desc
) infor on b.PO_NUMBER = infor.po_number
where b.PO_NUMBER = @po;

If I add "limit 1" to the left join subquery I don't get any results from the subquery.

TL;DR: I just want to update a column from Table A based on the latest value from Table B for a shared ID column between those 2 tables.

Answer 1

If I followed you correctly, you can use a correlated subquery to retrieve the latest sce_status from table infor_order for the po_number of planning_backloguov, like so:

update planning_backloguov pb
set pb.sce_status = (
    select io.sce_status
    from infor_order io
    where io.po_number = pb.po_number
    order by io.updated_at desc
    limit 1
)

---

If you need to update more than one column, then that's a different question. In that case, you need a join and filtering:

update planning_backloguov pb
inner join infor_order io on io.po_number = pb.po_number
set 
    pb.sce_status = io.sce_status,
    pb.some_other_column = io.some_other_column  -- change to the actual column name
where io.updated_at = (
    select max(io1.updated_at)
    from infor_order io1
    where io1.po_number = io.po_number
)