MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table products(
prod_id int,
prod_name text);
insert into products values
(1,'tv'),
(2,'radio'),
(3,'typewriter');
create table shipments(
prod_id int,
ship_id int,
ship_date date);
insert into shipments values
(1,22,'2022-12-12'),
(1,33,'2021-12-12'),
(1,52,'2021-01-10'),
(2,31,'2021-04-10'),
(2,36,'2021-09-10');
/* DELIMITER $$ */

CREATE PROCEDURE check_shipments()
BEGIN
DECLARE no_more_products int DEFAULT 0;
DECLARE p_id int DEFAULT 0;
DECLARE ship_times int DEFAULT 0;
DECLARE cur_shipments CURSOR FOR
    SELECT prod_id FROM shipments;
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_products = 1;
CREATE table IF NOT EXISTS products_Info(
       prod_id int primary key, 
       prod_shipments int);
OPEN cur_shipments;
FETCH cur_shipments INTO p_id;
REPEAT
    SELECT count(prod_id) INTO ship_times FROM shipments where prod_id = p_id;
    IF ship_times > 0 THEN
        INSERT INTO products_Info VALUES(p_id, 1)
           ON DUPLICATE KEY UPDATE 
           prod_shipments = prod_shipments + 1;
    END IF;
    FETCH cur_shipments INTO p_id;
UNTIL no_more_products = 1 END REPEAT;
CLOSE cur_shipments;
SELECT * FROM products_Info;
drop table products_Info;
END ;/*$$

DELIMITER ;*/
call check_shipments


prod_id
prod_shipments


1
3

2
2