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, 2, 3.99 FROM DUAL UNION ALL
SELECT 1, 2, 3, 3.99 FROM DUAL UNION ALL
SELECT 1, 2, 4, 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 UNION ALL
SELECT 1, 4, 8, 1.99 FROM DUAL UNION ALL
SELECT 1, 4, 9, NULL FROM DUAL UNION ALL
SELECT 1, 5, 1, NULL FROM DUAL UNION ALL
SELECT 1, 5, 2, NULL FROM DUAL;
13 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,
           COUNT(item_price)
             OVER ( PARTITION BY list_id, item_id ) AS num_non_null,
           COUNT(*)
             OVER ( PARTITION BY list_id, item_id ) AS num_locations
    FROM   table_name
  )
  WHERE ( min_price = max_price AND num_non_null = num_locations )
  OR    ( num_non_null = 0 )
) src
ON ( src.rid = dst.ROWID )
WHEN MATCHED THEN
  UPDATE SET location_id = 0
  DELETE WHERE src.rn > 1;
8 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

1
4
8
1.99

1
4
9
null

1
5
0
null

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
1	4	8	1.99
1	4	9	null
1	5	0	null