MySQL insert on duplicate update for non-PRIMARY key
You need a query that check if exists any row with you record_id (or person_id). If exists update it, else insert new row
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 better approach is
UPDATE table.person SET (...) WHERE person_id='SomeValue'
IF ROW_COUNT()=0
INSERT INTO table.person (...) VALUES (...)
Your question is very valid. This is a very common requirement. And most people get it wrong, due to what MySQL offers.
- The requirement: Insert unless the
PRIMARY
key exists, otherwise update. - The common approach:
ON DUPLICATE KEY UPDATE
- The result of that approach, disturbingly: Insert unless the
PRIMARY
or anyUNIQUE
key exists, otherwise update!
What can go horribly wrong with ON DUPLICATE KEY UPDATE
? You insert a supposedly new record, with a new PRIMARY
key value (say a UUID), but you happen to have a duplicate value for its UNIQUE
key.
What you want is a proper exception, indicating that you are trying to insert a duplicate into a UNIQUE
column.
But what you get is an unwanted UPDATE
! MySQL will take the conflicting record and start overwriting its values. If this happens unintentionally, you have mutilated an old record, and any incoming references to the old record are now referencing the new record. And since you probably won't tell the query to update the PRIMARY
column, your new UUID is nowhere to be found. If you ever encounter this data, it will probably make no sense and you will have no idea where it came from.
We need a solution to actually insert unless the PRIMARY
key exists, otherwise update.
We will use a query that consists of two statements:
- Update where the
PRIMARY
key value matches (affects 0 or 1 rows). - Insert if the
PRIMARY
key value does not exist (inserts 1 or 0 rows).
This is the query:
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. The UPDATE
is easy. As for the INSERT
: WHERE id = 1
results in a row if the id exists, or no row if it does not. HAVING COUNT(*) = 0
inverts that, resulting in a row if the id is new, or no row if it already exists.
I have explored other variants of the same idea, such as with a LEFT JOIN
and WHERE
, but they all looked more convoluted. Improvements are welcome.
13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an 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