Mysql insert multiple rows, if duplicate value column then update-CodePudding

I have a MySQL table that looks like this

id | customer_id | textkey | value |
--- ------------- --------- ------- 
 1 |           1 | text1   | value1|
 2 |           1 | text2   | value2|
 3 |           1 | text3   | value3|
 4 |           1 | text4   | value4|
 5 |           2 | text1   | value1|
...

I want to insert the following values to customer_id 1

(text1, valueX), (text3, valueY), (text5, value5), (text6, value6)

The end result should look like this

mysql> SELECT * from MyTable where customer_id=1;

id | customer_id | textkey | value |
--- ------------- --------- ------- 
 1 |           1 | text1   | valueX|
 2 |           1 | text2   | value2|
 3 |           1 | text3   | valueY|
 4 |           1 | text4   | value4|
 6 |           1 | text5   | value5|
 7 |           1 | text6   | value6|

The value column should update whenever there is a duplicate in the textkey insert, and insert regularly if the textkey is not a duplicate. What query could I use to achieve this? I tried using IGNORE, LAST_INSERT_ID(), and ON DUPLICATE KEY UPDATE but I cant figure out what works for a "ON DUPLICATE COLUMN UPDATE" scenario. Thanks

CodePudding user response：

You must create unique index which will detect the duplication:

CREATE UNIQUE INDEX idx ON test (customer_id, textkey);

Now you can use INSERT .. ODKU:

INSERT INTO test (customer_id, textkey, value) VALUES
(1, 'text1', 'valueX'), 
(1, 'text3', 'valueY'), 
(1, 'text5', 'value5'), 
(1, 'text6', 'value6')
ON DUPLICATE KEY UPDATE
value = VALUES(value);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6dcc351706f574f4c9f13c1fc95b9225