MySQL is a popular database software used by many individuals are organizations around the world. Sometimes you may need to insert a record in MySQL table or update it if it already exists. If you try to insert a record that already exists or has duplicate primary key value, MySQL will give you an error message. In this article, we will learn how to insert record if it doesn’t exist, or update it if it exists in MySQL.
MySQL INSERT or UPDATE if exists
The easiest way to do MySQL INSERT or UPDATE if exists is to use INSERT ON DUPLICATE command. Here is an example to insert values(1,2) into couple of columns(key, col1) in our table, where key is our primary key and col1 is for data. INSERT ON DUPLICATE statement will update col1 in case of duplicate key.
INSERT INTO table (key,col1) VALUES (1,2) ON DUPLICATE KEY UPDATE col1 = 2;
You can also use col1’s existing value to calculate the new value in case of duplicate key.
INSERT INTO table (key,col1) VALUES (1,2) ON DUPLICATE KEY UPDATE col1 = col1+1;
MySQL also provides a REPLACE INTO statement that allows you to replace a row if it has the same value as new row for PRIMARY KEY or UNIQUE constraint. In this case, REPLACE INTO statement will delete the old row before inserting the new one.
For example, if you use the following INSERT statement.
mysql> INSERT INTO messages (sender, sent_time, status) VALUES ('john', '2022-04-13', 'active');
you can use the following statement instead.
mysql> REPLACE INTO messages (sender, sent_time, status) VALUES ('john', '2022-04-13', 'active');
In the above case, however, deleting the old row can cause problems if it is a parent row of other tables. If there is a CASCADE on deletion enabled, then it will even delete rows from other tables.
In this article, we have learnt how to do INSERT or UPDATE if exists in MySQL. Obviously, INSERT ON DUPLICATE is more preferred over REPLACE INTO. Nevertheless, you can customize these statements as per your requirement. This INSERT or UPDATE is useful if you have large tables and automated insertions that you cannot keep a track of all the time.
Also read:
How to Do Regular Expression Replace in MySQL
How to Get Digits from String in MySQL
How to Use SCP with PEM File (SSH Key)
How to Ignore Certificate Errors in cURL
How to Get Creation Date for File & Folders in Linux