Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (id bigint, item_id text, qty numeric);
insert into test values 
  (2, 'abc', 12), (3, 'abc', 15), (5, 'def', 5), (8, 'ghi', 19), (9, 'abc', 10),
  (10, 'abc', 12), (11, 'abc', 15), (12, 'def', 5), (13, 'def', 5), (14, 'xyz', 19),
  (15, 'ghi', 13), (16, 'ghi', 19), (6, 'xyz', 38), (7, 'ghi', 64), (1, 'abc', 74), 
  (4, 'def', 20);

CREATE TABLE



INSERT 0 16


with newrecs as (
  select item_id, min(id) as id, sum(qty) as qty
    from test
   group by item_id
), updates as (
  update test t
     set qty = n.qty
    from newrecs n
   where (n.id, n.item_id) = (t.id, t.item_id)
  returning t.id
)
delete from test where id not in (select id from updates);

DELETE 12


select * from test;


id
item_id
qty


6
xyz
57

7
ghi
115

1
abc
138

4
def
35



SELECT 4