title: How to update with join multiple tables?
body: <p>How to update duplicate record and left only one that have max value.</p>
  <p>This is my query:</p>
  <pre><code>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(*)&gt;1);
  </code></pre>
  <p>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?</p>