CODEWITHSUNDEEP
sqloracle-database
JustToKnow
JustToKnow

Reputation: 823

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'

IMAGE

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

Upvotes: 0

Views: 81

Answers (3)

zealous
zealous

Reputation: 7503

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

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to update the rows where not exists an older row for the product:

update table_price_product pp
set status = 150
where not exists
(
  select null
  from table_price_product older
  where older.item_code = pp.item_code
  and older.request_id < pp.request_id
);

If you are worried about speed, you may want to provide this index:

create index idx on table_price_product (item_code, request_id);

For completeness sake: Saying that no older row exists can also be expressed as: You want to update the rows where the request ID is the minimum for the product. Hence:

update table_price_product pp
set status = 150
where (item_code, request_id) in
(
  select item_code, min(request_id)
  from table_price_product
  group by item_code
);

This query, too, can benefit from the suggested index.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

We can try using an update join here:

UPDATE table_price_product tpp1
INNER JOIN
(
    SELECT ITEM_CODE, MIN(REQUEST_ID) AS MIN_REQUEST_ID
    FROM table_price_product
    GROUP BY ITEM_CODE
) tpp2
    ON tpp1.ITEM_CODE = tpp2.ITEM_CODE AND
       tpp1.REQUEST_ID = tpp2.MIN_REQUEST_ID
SET
    STATUS = '150';

The logic here is that we use the tpp2 subquery to scope only records for each item code which have the minimum value for the request ID. Then, we update the status for those records only.

Upvotes: 1

Related Questions