Mysql insert multiple rows, if duplicate value column then update

Question

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

Does this answer your question? "mysql update if some values exists otherwise create a new entry", "MySQL 'UPDATE ON DUPLICATE KEY' without a unique column?" — outis
– outis, Commented Apr 26, 2022 at 7:37

Akina · Accepted Answer · 2022-04-26 04:14:43Z

2

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

answered Apr 26, 2022 at 4:14

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

blabla_bingo Over a year ago

If we don't add a unique key and capitalize on the ON DUPLICATE KEY UPDATE clause, would there be a way other than fetch and compare through a cursor in a sproc ?

Akina Over a year ago

@blabla_bingo Without unique key you must use 2 separate queries - INSERT and UPDATE. From the other side without this unique key the task makes no sense - in theory it is possible that newly inserted row interferes with 2 or more rows, and you have no criteria for to determine what row should be updated.

Collectives™ on Stack Overflow

Mysql insert multiple rows, if duplicate value column then update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related