Situation
Often you have the situation that you need to check if an table entry exists, before you can make an update. If it does not exist, you have to do an insert first.
The simple straightforward approach is this:
(The example is for an entry in the WordPress wp_postmeta table)
SELECT meta_id FROM wp_postmeta
WHERE post_id = ?id AND meta_key = ‘page_title’
If ResultCount == 0
INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
VALUES (?id, ‘page_title’, ?page_title)
Else
UPDATE wp_postmeta SET meta_value = ?page_title
WHERE post_id = ?id AND meta_key = ‘page_title’
Solution
This is not too bad, but we could actually combine everything into one query. I found different solutions on the internet. The simplest, but MySQL only solution is this:
INSERT INTO users (username, email) VALUES (‘Jo’, ‘jo@email.com’)
ON DUPLICATE KEY UPDATE email = 'jo@email.com'
Note that this the ‘ON DUPLICATE KEY’ statement only works on PRIMARY KEY and UNIQUE columns.
ON DUPLICATE KEY UPDATE
Basically INSERT with an ON DUPLICATE KEY UPDATE clause enables existing rows to be updated if a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY.
INSERT IGNORE
Using INSERT IGNORE effectively causes MySQL to ignore execution errors while attempting to perform INSERT statements. This means that an INSERT IGNORE statement which contains a duplicate value in a UNIQUE index or PRIMARY KEY field does not produce an error, but will instead simply ignore that particular INSERT command entirely. The obvious purpose is to execute a large number of INSERT statements for a combination of data that is both already existing in the database as well as new data coming into the system.
mysql> INSERT INTO users (user_id, username,nickname,`password`,salt,avatar_url) VALUES (1, "1","sw_nickname","sw_password","sw_salt","aaa");
ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'
mysql> INSERT IGNORE INTO users (user_id, username,nickname,`password`,salt,avatar_url) VALUES (1, "1","sw_nickname","sw_password","sw_salt","aaa");
Query OK, 0 rows affected, 1 warning (0.00 sec)
REPLACE
In the event that you wish to actually replace rows where INSERT commands would produce errors due to duplicate UNIQUE or PRIMARY KEY values as outlined above, one option is to opt for the REPLACE statement.
When issuing a REPLACE statement, there are two possible outcomes for each issued command:
- No existing data row is found with matching values and thus a standard
INSERTstatement is performed. - A matching data row is found, causing that existing row to be deleted with the standard
DELETEstatement, then a normalINSERTis performed afterward.
For example, we can use REPLACE to swap out our existing record of id = 1 of In Search of Lost Time by Marcel Proust with Green Eggs and Ham by Dr. Seuss:
mysql> REPLACE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)
Notice that even though we only altered one row, the result indicates that two rows were affected because we actually DELETED the existing row then INSERTED the new row to replace it.
Reference
- https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
- https://remy.supertext.ch/2010/11/mysqlupdate-and-insert-if-not-exists/
- https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/