如何更新重復記錄并只留下一個具有最大值的記錄。
這是我的查詢：
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);
但在這種情況下，它會更新所有不是這個最大值的東西，但我希望從這個條件下更新所有不是最大值的東西。因此，如果我有 3 條記錄，我只想更新 2 條它們的值較小。有什么建議嗎？
uj5u.com熱心網友回復：
嘗試這個 ：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/439761.html
標籤：sql PostgreSQL
上一篇：按月/年和id獲取最后一條記錄
下一篇：哪個視窗函式更快？