Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( list_id, item_id, location_id, item_price ) AS
SELECT 1, 1, 1, 1.99 FROM DUAL UNION ALL
SELECT 1, 1, 2, 1.99 FROM DUAL UNION ALL
SELECT 1, 1, 3, 1.99 FROM DUAL UNION ALL
SELECT 1, 2, 1, 3.99 FROM DUAL UNION ALL
SELECT 1, 2, 2, 3.99 FROM DUAL UNION ALL
SELECT 1, 2, 3, 3.99 FROM DUAL UNION ALL
SELECT 1, 3, 1, 5.99 FROM DUAL UNION ALL
SELECT 1, 3, 2, 7.99 FROM DUAL UNION ALL
SELECT 1, 3, 3, 8.99 FROM DUAL;
9 rows affected

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         rn
  FROM   (
    SELECT ROW_NUMBER() OVER ( PARTITION BY list_id, item_id ORDER BY location_id )
             AS rn,
           MIN( item_price ) OVER ( PARTITION BY list_id, item_id ) AS min_price,
           MAX( item_price ) OVER ( PARTITION BY list_id, item_id ) AS max_price
    FROM   table_name
  )
  WHERE min_price = max_price
) src
ON ( src.rid = dst.ROWID )
WHEN MATCHED THEN
  UPDATE SET location_id = 0
  DELETE WHERE src.rn > 1;
6 rows affected

SELECT * FROM table_name;


LIST_ID
ITEM_ID
LOCATION_ID
ITEM_PRICE


1
1
0
1.99

1
2
0
3.99

1
3
1
5.99

1
3
2
7.99

1
3
3
8.99