Updating only specific rows within the lowest value

I’m trying to update different rows in my table.

I got a table called: table_price_product

this table contains all the information related to different products

NOTE: inside this table you will see different rows for the same product (or not) because it contains all the prices for all products (old and new ones)

Every item_code has a different “request_id”, the newest row will have the highest value.

Take a look at this table:

|ITEM_CODE |PRICE    |START_DATE_ACTIVE |END_DATE_ACTIVE   |INSERT_UPDATE_FLAG    | STATUS| REQUEST_ID |
|'99342435'|'9999'   |null              |null              |I                     |NEW    |'42937536'  |
|'91123323'|'1049.67'|null              |null              |I                     |NEW    |'42455454'  |
|'89992424'|'18799'  |null              |null              |I                     |NEW    |'42886567'  |
|'99342435'|'9800'   |null              |null              |I                     |NEW    |'42937524'  |
|'91123323'|'900.67' |null              |null              |I                     |NEW    |'42455447'  |
|'89992424'|'17499'  |null              |null              |I                     |NEW    |'42886541'  |

This is my sqlfiddle

I want to create an UPDATE script in order to modify the item_codes within the lowest request id

In this case, the item_code: ‘99342435’ has 2 rows: one with the request_id = ‘42937536’ and the other one with the request_id = ‘42937524’. I would like to update the row with the lowest request_id and set the attribute ‘status’ = ‘150’

---

I would like to obtain this output:

|ITEM_CODE |PRICE    |START_DATE_ACTIVE |END_DATE_ACTIVE   |INSERT_UPDATE_FLAG    | STATUS| REQUEST_ID |
|'99342435'|'9999'   |null              |null              |I                     |NEW    |'42937536'  |
|'91123323'|'1049.67'|null              |null              |I                     |NEW    |'42455454'  |
|'89992424'|'18799'  |null              |null              |I                     |NEW    |'42886567'  |
|'99342435'|'9800'   |null              |null              |I                     |150    |'42937524'  |
|'91123323'|'900.67' |null              |null              |I                     |150    |'42455447'  |
|'89992424'|'17499'  |null              |null              |I                     |150    |'42886541'  |

I tried to do something like this but it did not work.:

max(request_id) over (partition by item_code) as max_request

Can help me to solve this?

EDIT#1: i’m using Oracle database

---

---

EDIT: I could have this scenario:

|ITEM_CODE |PRICE    |START_DATE_ACTIVE |END_DATE_ACTIVE   |INSERT_UPDATE_FLAG    | STATUS| REQUEST_ID |
|'99342435'|'9999'   |null              |null              |I                     |NEW    |'42937536'  |
|'99342435'|'9800'   |null              |null              |I                     |NEW    |'42937524'  |
|'99342435'|'9800'   |null              |null              |I                     |NEW    |'42937512'  |

I would like to obtain this:

|ITEM_CODE |PRICE    |START_DATE_ACTIVE |END_DATE_ACTIVE   |INSERT_UPDATE_FLAG    | STATUS| REQUEST_ID |
|'99342435'|'9999'   |null              |null              |I                     |NEW    |'42937536'  |
|'99342435'|'9800'   |null              |null              |I                     |150    |'42937524'  |
|'99342435'|'9800'   |null              |null              |I                     |150    |'42937512'  |

Answer

You can try the following.

Update using min()

update table_price_product
set status = '150'
where REQUEST_ID in
(
    select 
       REQUEST_ID
    from
    (
        select
            ITEM_CODE,
            min(REQUEST_ID) as request_id
        from table_price_product
        group by
            ITEM_CODE
    ) val

);

After update statement when you do select as following then you get your output as expected.

select *
from table_price_product

Output:

| ITEM_CODE | PRICE   | INSERT_UPDATE_FLAG | STATUS | REQUEST_ID |
| --------- | ------- | ------------------ | ------ | ---------- |
| 99342435  | 9999    | I                  | NEW    | 42937536   |
| 91123323  | 1049,67 | I                  | NEW    | 42937536   |
| 89992424  | 18799   | I                  | NEW    | 42937536   |
| 99342435  | 9800    | I                  | 150    | 42935836   |
| 91123323  | 900,67  | I                  | 150    | 42930236   |
| 89992424  | 17499   | I                  | 150    | 42936936   |

---

EDIT: I could have this scenario:

|ITEM_CODE |PRICE |START_DATE_ACTI

VE |END_DATE_ACTIVE   |INSERT_UPDATE_FLAG    | STATUS| REQUEST_ID |
|'99342435'|'9999'   |null              |null              |I                     |NEW    |'42937536'  |
|'99342435'|'9800'   |null              |null              |I                     |NEW    |'42937524'  |
|'99342435'|'9800'   |null              |null              |I                     |NEW    |'42937512'  |

I would like to obtain this:

|ITEM_CODE |PRICE    |START_DATE_ACTIVE |END_DATE_ACTIVE   |INSERT_UPDATE_FLAG    | STATUS| REQUEST_ID |
|'99342435'|'9999'   |null              |null              |I                     |NEW    |'42937536'  |
|'99342435'|'9800'   |null              |null              |I                     |150    |'42937524'  |
|'99342435'|'9800'   |null              |null              |I                     |150    |'42937512'  |