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' |
Advertisement
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' |