MySQL 5.7

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

select version();


version()


5.7.32


CREATE TABLE shipping(id int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, origin INT(10),
order_id int(10), createdAt date NOT NULL, product_id varchar(100) NOT NULL,
amount decimal(10,0));

CREATE TABLE reprint (id int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
product_id int(50), reprintAt varchar(255), status tinyint(4));


 



INSERT INTO shipping (origin, order_id, createdAt, product_id, amount)
VALUES (1, 11, NOW(), 234, 2000),
(1, 11, NOW(), 235, 3000),
(1, 11, NOW(), 236, 4000),
(2, 12, NOW(), 236, 3000),
(2, 12, NOW(), 235, 2100),
(3, 13, NOW(), 236, 2200),
(3, 13, NOW(), 239, 3400),
(4, 14, NOW(), 237, 2300),
(4, 14, NOW(), 233, 4000);


INSERT INTO reprint (product_id, reprintAt, status)
VALUES (234, NOW() + INTERVAL 1 DAY, 1),
(235, NOW() + INTERVAL 2 DAY, 1),
(236, NOW(), 1),
(237, NOW(), 1),
(238, NOW(), 1),
(239, NOW(), 1),
(240, NOW(), 1),
(233, NOW(), 1);

select * from shipping;
select * from reprint;



id
origin
order_id
createdAt
product_id
amount


1
1
11
2020-12-22
234
2000

2
1
11
2020-12-22
235
3000

3
1
11
2020-12-22
236
4000

4
2
12
2020-12-22
236
3000

5
2
12
2020-12-22
235
2100

6
3
13
2020-12-22
236
2200

7
3
13
2020-12-22
239
3400

8
4
14
2020-12-22
237
2300

9
4
14
2020-12-22
233
4000




id
product_id
reprintAt
status


1
234
2020-12-23 11:20:33
1

2
235
2020-12-24 11:20:33
1

3
236
2020-12-22 11:20:33
1

4
237
2020-12-22 11:20:33
1

5
238
2020-12-22 11:20:33
1

6
239
2020-12-22 11:20:33
1

7
240
2020-12-22 11:20:33
1

8
233
2020-12-22 11:20:33
1


DECLARE test_1 INT DEFAULT 0;
DECLARE test_2 INT DEFAULT 0;

SELECT count(distinct product_id) INTO test_1 FROM shipping;

SELECT count(distinct product_id) INTO test_2 FROM reprint;

IF test_1 != test_2 THEN

INSERT INTO shipping (id, origin, order_id, createdAt, product_id, amount) 
SELECT LAST_INSERT_ID(), reprint.id, 16, reprint.reprintAt, reprint.product_id, 4000 
FROM
    reprint

    UNION 
       SELECT * FROM shipping
    
ON duplicate key update product_id = values(product_id);


SELECT * from shipping;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE test_1 INT DEFAULT 0;
DECLARE test_2 INT DEFAULT 0;

SELECT count(distin' at line 1

id	origin	order_id	createdAt	product_id	amount
1	1	11	2020-12-22	234	2000
2	1	11	2020-12-22	235	3000
3	1	11	2020-12-22	236	4000
4	2	12	2020-12-22	236	3000
5	2	12	2020-12-22	235	2100
6	3	13	2020-12-22	236	2200
7	3	13	2020-12-22	239	3400
8	4	14	2020-12-22	237	2300
9	4	14	2020-12-22	233	4000

id	product_id	reprintAt	status
1	234	2020-12-23 11:20:33	1
2	235	2020-12-24 11:20:33	1
3	236	2020-12-22 11:20:33	1
4	237	2020-12-22 11:20:33	1
5	238	2020-12-22 11:20:33	1
6	239	2020-12-22 11:20:33	1
7	240	2020-12-22 11:20:33	1
8	233	2020-12-22 11:20:33	1