MySQL 8.0

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

select version();


version()


8.0.36


create table items (
id int,
item_type int);

insert into items values 
(1,1),
(2,1),
(3,1),
(4,1),
(5,1),
(6,2),
(7,2),
(8,2),
(9,3),
(10,3),
(11,3),
(12,3),
(13,3),
(14,4),
(15,4);

create table item_groups  (
id int,
item_type int,
quantity int,
unique key `item_type`(`item_type`) );

insert into item_groups values 
(1,1,5),
(2,2,3),
(3,3,5),
(4,4,2);





Records: 15  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0


insert into item_groups (item_type,
                         quantity) 
select * from ( select item_type , count(*) as new_quantity  from items group by item_type ) as tbl 
 ON DUPLICATE KEY UPDATE quantity = new_quantity;

Records: 4  Duplicates: 0  Warnings: 0