'How to update with join multiple tables?

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?

sqlpostgresql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions