Insert into a MySQL table or update if exists

While inserting rows into a table, there are times when the requirement is to insert the rows into a table, but if the key already exists, then update the value. This concept is also known as UPSERT, which is combining UPDATE with INSERT. In this article, we will be looking into :

1. Insert or Update into MySQL Table : using On Duplicate Key Update
2. Insert or Update into MySQL Table : using IGNORE INTO
3. Insert or Update into MySQL Table : using REPLACE INTO
4. MySQL Insert or Update if exists without primary key
   1. MySQL Insert or Update : UNIQUE KEY
   2. MySQL Insert or Update conditional : NOT EXISTS

We will be looking into all of them with detailed examples but first, let us look into the dataset. Assume the table we will be working with is customer_data and below data already exist in it.

Insert or Update into MySQL Table : using On Duplicate Key Update

Now let’s say we want to insert the row with customer_id = 2. Figure 1.1 shows that this already exists. Using the classic insert statement, we will be getting an error, observe the query and the action output message.

INSERT INTO customer_data (customer_id, customer_name, customer_place) VALUES (2, "Vaani","Denver");

Action Output Message : 23:39:39 INSERT INTO customer_data (customer_id, customer_name, customer_place) VALUES(2, “Vaani”,”Denver”) Error Code: 1062. Duplicate entry ‘2’ for key ‘customer_data.customer_id’ 0.00047 sec

Let us now see what difference it makes using ON DUPLICATE KEY UPDATE. Notice the query and the action output message.

INSERT INTO customer_data (customer_id, customer_name, customer_place) 
VALUES(2, "Vaani","Denver") ON DUPLICATE KEY UPDATE customer_name = "Hevika", customer_place = "Denver";

Action Output Message : 14:26:12 INSERT INTO customer_data (customer_id, customer_name, customer_place) VALUES(2, “Vaani”,”Denver”) ON DUPLICATE KEY UPDATE customer_name = “Hevika”, customer_place = “Denver” 2 row(s) affected 0.0037 sec

It says 2 rows affected as ON DUPLICATE KEY UPDATE displays the affected-rows value:

• 1 if the row is inserted.
• 2 if an existing row is updated.
• 0 if an existing row is set to its current values.

Let us see how the row with customer_id = 2 looks now. Select * on customer_data will give the below output.

It shows that since the key value is duplicated, query updated the row with new customer_name and customer_place values.

Read more about ON DUPLICATE KEY UPDATE in the official documentation.

Insert or Update into MySQL Table : using IGNORE INTO

Let us now move on to IGNORE INTO. Assume that we have the same data in the customer_data table, as shown in figure 1.1. We will be using an example to try and insert the row with customer_id = 2. 

INSERT IGNORE INTO customer_data (customer_id, customer_name, customer_place) VALUES (2, "Hevika","Denver");

On running the above query, we get only a warning message and not an error, but nothing gets changed in the table if we see the results. Notice the below two images of the action output message and the table data in figure 1.3 and figure 1.4, respectively.

Action Output Message :

Insert or Update into MySQL Table : using REPLACE INTO

REPLACE works similar to INSERT. The difference is: If the new row to be inserted has the same value of the PRIMARY KEY or the UNIQUE index as the existing row, in that case, the old row gets deleted first before inserting the new one.

We will be using the same example from customer_data table. Observe the below query with an action output message received after running it. 

REPLACE INTO customer_data(customer_id, customer_name, customer_place) VALUES(2, "Hevika","Atlanta");

Action Output Message : 18:27:57 REPLACE INTO customer_data (customer_id, customer_name, customer_place) VALUES(2, “Hevika”,”Atlanta”) 2 row(s) affected 0.0023 sec

Doing a select * again on customer_data gives the data as shown in figure 1.5. It shows that customer_name and customer_place got changed in the row with customer_id=2. Also, the action message output said 2 rows affected, which means one got deleted before inserting the other.

Read more about REPLACE in the official documentation.

MySQL Insert or Update if exists without primary key

In all the above examples, we worked on the data which had inserts and updates based on the primary key. What if I would like to check if a field that is not a primary key exists or not and would like to update or insert the table? There are two options for the same:

MySQL Insert or Update : UNIQUE KEY

Make the column UNIQUE. Now we can use the ON DUPLICATE KEY to perform the insert/update refer section Insert or Update into MySQL Table : using On Duplicate Key Update.

MySQL Insert or Update conditional : NOT EXISTS

In this section, we will be using the NOT EXISTS keyword. Example: We want to add one more row to our table customer_data (refer figure 1.1) or update based on customer_name. Observe the below query.

INSERT INTO customer_data (customer_id, customer_name, customer_place)
SELECT * FROM (SELECT 6, "Rasmus","TestPlace") AS tmp_name
WHERE NOT EXISTS (
    SELECT customer_name FROM customer_data WHERE customer_name = "Rasmus"
) LIMIT 1;

Since we do not yet have a row with customer_id = 6; the query will insert a new row on running this query. Select * on customer_data to verify.

To have a better understanding of the concept, let us re-run the same query, this time. We already have the row with customer_name = “Rasmus,” Hence, we get an action output message.

Action Output Message : 17:22:35 INSERT INTO customer_data (customer_id, customer_name, customer_place) SELECT * FROM (SELECT 6, “Rasmus”,”TestPlace”) AS tmp_name WHERE NOT EXISTS ( SELECT customer_name FROM customer_data WHERE customer_name = “Rasmus” ) LIMIT 1 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.00047 sec.

0 rows affected, so nothing new gets inserted.

We hope this article helped you with insert or update related queries. Good Luck !!