How to update with join multiple tables?-CodePudding

How to update duplicate record and left only one that have max value.

This is my query:

update
    omt_order_item
set
    status = 'CANCELED',
    substatus = 'CANCELED'
where
    order_item_num not in(
    select
        max(ooi.order_item_num)
    from
        omt_order_item ooi
    inner join omt_order_item_att ooia on
        ooi.order_item_num = ooia.order_item_num
    inner join omt_order_item_att ooia2 on
        ooi.order_item_num = ooia2.order_item_num
    where
        ooi.pfamily_type_code = 'B2C_FEAT'
        and ooia.att_name = 'ACTIVATION_DATES'
        and ooia.att_value = '10.02.2022'
        and ooia2.att_name = 'OGLAS_ID'
        and ooi.status = 'ACKNOWLEDGED'
        and ooi.substatus = 'NEW'
    group by
        ooi.fr_acount_id,
        ooia.att_value ,
        ooia2.att_value
    having
        count(*)>1);

But it this case it update all that is not this max value, but i want that update everything from this condition that is not max value. So if i have 3 records i want to update only 2 that they are smaller value. Any suggestion?

CodePudding user response：

Try this :

WITH list AS (
select
    ooi.fr_acount_id,
    max(ooi.order_item_num) AS max_order_item_num
from
    omt_order_item ooi
inner join omt_order_item_att ooia on
    ooi.order_item_num = ooia.order_item_num
inner join omt_order_item_att ooia2 on
    ooi.order_item_num = ooia2.order_item_num
where
    ooi.pfamily_type_code = 'B2C_FEAT'
    and ooia.att_name = 'ACTIVATION_DATES'
    and ooia.att_value = '10.02.2022'
    and ooia2.att_name = 'OGLAS_ID'
    and ooi.status = 'ACKNOWLEDGED'
    and ooi.substatus = 'NEW'
group by
    ooi.fr_acount_id,
    ooia.att_value ,
    ooia2.att_value
having
    count(*)>1
)
update
    omt_order_item AS ooi
set
    status = 'CANCELED',
    substatus = 'CANCELED'
from list AS l
where ooi.fr_acount_id = l.fr_acount_id
  and ooi.order_item_num <> l.max_order_item_num