CODEWITHSUNDEEP
sqlsql-serverdatesql-updateupdates
Bahaa Gerges
Bahaa Gerges

Reputation: 43

SQL Updating records based on most recent date

I am trying to update two fields (UNIT_COST) from (LST_RECV_COST) and VEND_ITEM_NO) from same filed (VEND_ITEM_NO).

All fields in one table (PO_VEND_ITEM). The table has sequence rows sorted by date filed (LST_RECV_DAT).

I want to update the data with record in the most recent date row. I have used the following code

UPDATE
PO_VEND_ITEM
SET
UNIT_COST = LST_RECV_COST,
VEND_ITEM_NO = VEND_ITEM_NO,
WHERE
LST_RECV_DAT = (SELECT MAX(LST_RECV_DAT)

It always get any error message. I am new to sql and do not know which code can work.

Could you advise please?

Upvotes: 1

Views: 690

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

use below DML to update table based on max() field

update PO_VEND_ITEM
set UNIT_COST = LST_RECV_COST   
from
    (select max(LST_RECV_DAT) LST_RECV_DAT, 
    VEND_ITEM_NO from PO_VEND_ITEM
    group by VEND_ITEM_NO) t1
where VEND_ITEM_NO = t1.VEND_ITEM_NO and t1.LST_RECV_DAT = LST_RECV_DAT

Upvotes: 0

DineshDB
DineshDB

Reputation: 6193

You have some syntax mistakes.

Try this.

UPDATE PO_VEND_ITEM
SET UNIT_COST    = LST_RECV_COST,
    VEND_ITEM_NO = VEND_ITEM_NO
WHERE LST_RECV_DAT = (SELECT MAX(LST_RECV_DAT) FROM PO_VEND_ITEM)

Upvotes: 1

Related Questions