Insert MySQL in a double update for a key other than PRIMARY

Question

I am a little confused with inserting an update request into a duplicate. I have a MySQL table with a structure like this:

• record_id (PRIMARY, UNIQUE)
• person_id (UNIQUE)
• some_text
• some_other_text

I want to update the values ​​of some_text and some_other_text for a person if it exists in my table.person or insert a new record in this table otherwise. How can this be done if person_id is not BASIC?

Answer 1

You need a query that checks if there is any row with you record_id (or person_id). If an update exists, add a new line

IF EXISTS (SELECT * FROM table.person WHERE record_id='SomeValue') UPDATE table.person SET some_text='new_some_text', some_other_text='some_other_text' WHERE record_id='old_record_id' ELSE INSERT INTO table.person (record_id, person_id, some_text, some_other_text) VALUES ('new_record_id', 'new_person_id', 'new_some_text', 'new_some_other_text') 

Another best approach is

 UPDATE table.person SET (...) WHERE person_id='SomeValue' IF ROW_COUNT()=0 INSERT INTO table.person (...) VALUES (...) 

Answer 2

Your question is very important. This is a very common requirement. And most people make mistakes because of what MySQL offers.

• Requirement: Insert if the PRIMARY key exists, otherwise it is updated .
• General Approach: ON DUPLICATE KEY UPDATE
• The result of this approach is alarming: Insert if the PRIMARY key or any UNIQUE exists, otherwise it is updated !

What could go horribly wrong with ON DUPLICATE KEY UPDATE ? You are inserting a supposedly new record with a new PRIMARY key value (for example, UUID), but you have a duplicate value for your UNIQUE key.

What you want is the correct exception, indicating that you are trying to insert a duplicate in a UNIQUE column.

But what you get is undesirable UPDATE ! MySQL will take an inconsistent record and begin to rewrite its values. If this happens unintentionally, you ruined the old record, and any incoming links to the old record now refer to the new record. And since you probably won't tell you to update the PRIMARY column, your new UUID will not be found anywhere. If you ever come across this data, it probably doesn’t make sense, and you won’t understand where it came from.

We need a solution to actually insert if the PRIMARY key does not exist, otherwise an update .

We will use a query consisting of two operators:

• Refresh where the value of the PRIMARY key matches (affects 0 or 1 row).
• Insert if the PRIMARY key value does not exist (inserts 1 or 0 rows).

This is the request:

 UPDATE my_table SET unique_name = 'one', update_datetime = NOW() WHERE id = 1; INSERT INTO my_table SELECT 1, 'one', NOW() FROM my_table WHERE id = 1 HAVING COUNT(*) = 0; 

Only one of these queries will have an effect. UPDATE easy. As for INSERT : WHERE id = 1 , the result is a string if id exists, or there is no row if it is not specified. HAVING COUNT(*) = 0 inverts, resulting in a string if the identifier is new, or no string if it already exists.

I explored other variations of the same idea, such as with LEFT JOIN and WHERE , but they all looked more confusing. Improvements are welcome.

Answer 3

13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax

If you specify ON DUPLICATE KEY UPDATE and a row is inserted that will duplicate the value in the UNIQUE or PRIMARY KEY index, MySQL performs the UPDATE of the old row.

Example:

 DELIMITER // DROP PROCEDURE IF EXISTS `sp_upsert`// DROP TABLE IF EXISTS `table_test`// CREATE TABLE `table_test` ( `record_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `person_id` INT UNSIGNED NOT NULL, `some_text` VARCHAR(50), `some_other_text` VARCHAR(50), UNIQUE KEY `record_id_index` (`record_id`), UNIQUE KEY `person_id_index` (`person_id`) )// INSERT INTO `table_test` (`person_id`, `some_text`, `some_other_text`) VALUES (1, 'AAA', 'XXX'), (2, 'BBB', 'YYY'), (3, 'CCC', 'ZZZ')// CREATE PROCEDURE `sp_upsert`( `p_person_id` INT UNSIGNED, `p_some_text` VARCHAR(50), `p_some_other_text` VARCHAR(50) ) BEGIN INSERT INTO `table_test` (`person_id`, `some_text`, `some_other_text`) VALUES (`p_person_id`, `p_some_text`, `p_some_other_text`) ON DUPLICATE KEY UPDATE `some_text` = `p_some_text`, `some_other_text` = `p_some_other_text`; END// DELIMITER ; mysql> CALL `sp_upsert`(1, 'update_text_0', 'update_text_1'); Query OK, 2 rows affected (0.00 sec) mysql> SELECT -> `record_id`, -> `person_id`, -> `some_text`, -> `some_other_text` -> FROM -> `table_test`; +-----------+-----------+---------------+-----------------+ | record_id | person_id | some_text | some_other_text | +-----------+-----------+---------------+-----------------+ | 1 | 1 | update_text_0 | update_text_1 | | 2 | 2 | BBB | YYY | | 3 | 3 | CCC | ZZZ | +-----------+-----------+---------------+-----------------+ 3 rows in set (0.00 sec) mysql> CALL `sp_upsert`(4, 'new_text_0', 'new_text_1'); Query OK, 1 row affected (0.00 sec) mysql> SELECT -> `record_id`, -> `person_id`, -> `some_text`, -> `some_other_text` -> FROM -> `table_test`; +-----------+-----------+---------------+-----------------+ | record_id | person_id | some_text | some_other_text | +-----------+-----------+---------------+-----------------+ | 1 | 1 | update_text_0 | update_text_1 | | 2 | 2 | BBB | YYY | | 3 | 3 | CCC | ZZZ | | 5 | 4 | new_text_0 | new_text_1 | +-----------+-----------+---------------+-----------------+ 4 rows in set (0.00 sec) 

SQL Fiddle Demo

Answer 4

How about my approach?

Let's say you have one table with an auto-increment id and three text columns. You want to insert / update the value of column3 with the values ​​in column1 and column2, which is a (not unique) key.

I use this query (without explicitly locking the table):

 insert into myTable (id, col1, col2, col3) select tmp.id, 'col1data', 'col2data', 'col3data' from (select id from myTable where col1 = 'col1data' and col2 = 'col2data' union select null as id limit 1) tmp on duplicate key update col3 = values(col3) 

Is there something wrong with this? For me it works the way I want.