CODEWITHSUNDEEP
sqloracle-databaseplsql
SausageBuscuit
SausageBuscuit

Reputation: 1296

PL/SQL - Update Rows with Max Date Using Value from another table

I have an Oracle database where I'm working with two tables, as shown below.

ITEMS

ITEM_ID  |  ITEM_DESC     |   ITEM_STATUS
============================================
1        |  ITEM 1        |       A
2        |  ITEM 2        |       A
3        |  ITEM 3        |       I
4        |  ITEM 4        |       A

ITEM_UPDATES

ITEM_ID  |  LAST_CHANGE     |   ITEM_STATUS
=============================================
1        |  1/21/2010       |
1        |  4/1/2015        |
2        |  1/21/2010       |
2        |  7/14/2016       |
3        |  1/21/2010       |
3        |  10/21/2011      |
3        |  11/15/2017      |
4        |  11/30/2010      |

We are wanting to change the way that ITEM_STATUS is tracked in this system, and I'm trying to move the ITEM_STATUS column to the ITEM_UPDATES table. Things that occur in the past don't matter and will likely have unique status, however I want to set ITEM_STATUS for each record with a MAX(LAST_CHANGE) for a given ID to the value of the ITEM_STATUS column currently in ITEMS. So basically, the finished table would look like this.

ITEM_UPDATES

ITEM_ID  |  LAST_CHANGE     |   ITEM_STATUS
=============================================
1        |  1/21/2010       |       
1        |  4/1/2015        |       A
2        |  1/21/2010       |
2        |  7/14/2016       |       A
3        |  1/21/2010       |
3        |  10/21/2011      |
3        |  11/15/2017      |       I
4        |  11/30/2010      |       A

I have the query to select the proper data below, but I don't know how to translate this into an update statement given that I'm having to compare item_ids AND whether or not something is the max date record for that item. Is this doable?

  SELECT ITEM_UPDATES.ITEM_ID, ITEMS.ITEM_STATUS, MAX(EFFECTIVE_DATE) AS MAX_DATE
    FROM ITEM_UPDATES, ITEMS
   WHERE ITEM_UPDATES.ITEM_ID = ITEMS.ITEM_ID  
GROUP BY ITEM_UPDATES.ITEM_ID, ITEMS.ITEM_STATUS

Upvotes: 1

Views: 2373

Answers (2)

Martin Preiss
Martin Preiss

Reputation: 396

Maybe:

update item_updates iup
   set iup.item_status = (select item_status ist
                            from ist.item_id = iup.item_id)
 where (iup.item_id, iup.last_change) = (select iup2.item_id, max(iup.last_change)
                                           from item_updates iup2
                                          where iup2.item_id = iup.item_id
                                          group by iup2.item_id)

Now that I see Gordon Linoff's answer, I aks myself why I added the (already correlated) item_id...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270583

So you want the status updated on the most recent item_updates record. You can do:

update item_updates iu
    set item_status = (select i.item_status from items where i.item_id = iu.item_id)
    where iu.effective_date = (select max(iu2.effective_date)
                               from item_updates iu2
                               where iu2.item_id = iu.item_id
                              );

Upvotes: 2

Related Questions