CODEWITHSUNDEEP
mysqlsqlamazon-aurora
dahuin
dahuin

Reputation: 87

Is there any way to bulk many upserts to mysql db?

The current process is doing an upsert like the code below.

However, the high number of requests seems to cause problems.

Is there a way to do it with fewer requests?

1.

INSERT INTO example_table (id, prop_a, prop_b) VALUES (1,1,7) 
ON DUPLICATE KEY UPDATE prop_a=1,prop_b=7;

-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       7       0
-------------------------------------------------------------

INSERT INTO example_table (id, prop_b, prop_c) VALUES (1,5,8) 
ON DUPLICATE KEY UPDATE prop_b=5,prop_c=8;
-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       5       8
-------------------------------------------------------------

INSERT INTO example_table (id, prop_a, prop_b) VALUES (7,3,3) 
ON DUPLICATE KEY UPDATE prop_a=3,prop_b=3;
-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       5       8
7                3       3       0
-------------------------------------------------------------

Upvotes: 0

Views: 115

Answers (1)

Akina
Akina

Reputation: 42834

INSERT INTO example_table (id, prop_a, prop_b, prop_c) 
SELECT 1,1,7,0 UNION ALL
SELECT 1,5,0,8 UNION ALL
SELECT 7,3,3,0
ON DUPLICATE KEY UPDATE 
prop_a=CASE WHEN VALUES(prop_a) = 0 
            THEN COALESCE(prop_a, 0)
            ELSE VALUES(prop_a) END,
prop_b=CASE WHEN VALUES(prop_b) = 0 
            THEN COALESCE(prop_b, 0)
            ELSE VALUES(prop_b) END,
prop_c=CASE WHEN VALUES(prop_c) = 0 
            THEN COALESCE(prop_c, 0)
            ELSE VALUES(prop_c) END;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=64a0ef6520c65de8ac7e97ea90dc40d5

Upvotes: 1

Related Questions