let's say i have 2 tables table shipping 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

put on duplicate key update in MySQL

let’s say i have 2 tables

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);

table shipping

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

table reprint

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

so there are 2 tables, with the same product_id. if the number of product_id from reprint and shipping are not the same, then put some record contain missing product_id into shipping. In this case, product_Id ‘238’ and ‘240’ are not included in table shipping, so I must insert a new record contain product_id ‘238’ and ‘240’ into the shipping table.

for those requirements, the expected results should be going this (pls take a look at product_id 238 and 240 on this table)

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
10	5	16	2020-12-22	238	4000
11	7	16	2020-12-22	240	4000

I’m trying with this query

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);

but it’s says

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

this is the fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c489fdb3c6c07bb597bf23928ab97397

Answer

If I follow you correctly, you can just use not exists:

insert into shipping (origin, order_id, createdat, product_id, amount) 
select id, 16, reprintat, product_id, 4000
from reprint r
where not exists (select 1 from shipping s where s.product_id = r.product_id)

This creates a new row in shipping for each row in reprint whose product does not yet exist in the target yet, with constant amounts and order id.

I don’t think that on duplicate key makes sense in this context, since the product is not a unique column.

Advertisement

Answer