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