Open In App

INSERT ON DUPLICATE KEY UPDATE in MySQL

Last Updated : 23 Dec, 2020
Improve
Improve
Like Article
Like
Save
Share
Report

INSERT ON DUPLICATE KEY UPDATE statement is available in MySQL as an extension to the INSERT statement. Whenever a new row is inserted into a table in case the row causes a duplicate entry in the UNIQUE index or PRIMARY KEY, MySQL will throw an error.

When the ON DUPLICATE KEY UPDATE option is defined in the INSERT statement, the existing rows are updated with the new values instead.

Syntax :

INSERT INTO table (column_names)
VALUES (values)
ON DUPLICATE KEY UPDATE
  col1 = val1,  
  col2 = val2 ;

Along with the INSERT statement, ON DUPLICATE KEY UPDATE statement defines a list of column & value assignments in case of duplicate.

How it works :
The statement first attempts to insert a new row into the table. When a duplicate entry is their error occurs, MySQL will update the existing row with the value specified in the ON DUPLICATE KEY UPDATE clause.

Example –
Let us create a table named ‘geek_demo’ as follows.

CREATE TABLE geek_demo
(
id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(100) 
);

Inserting data into geek_demo :

INSERT INTO geek_demo (name)
VALUES('Neha'), ('Nisha'), ('Sara') ;

Reading data from table :

SELECT id, name
FROM geek_demo;

Output :

id name
1 Neha
2 Nisha
3 Sara

Now, one row will insert into the table.

INSERT INTO geek_demo(name) 
VALUES ('Sneha') 
ON DUPLICATE KEY UPDATE name = 'Sneha';

As there was no duplicate, MySQL inserts a new row into the table. The output of the above statement is similar to the output below statement as follows.

INSERT INTO geek_demo(name)
VALUES ('Sneha');

Reading data :

SELECT id, name
FROM geek_demo; 

Output :

id name
1 Neha
2 Nisha
3 Sara
4 Sneha

Let us insert a row with a duplicate value in the id column as follows.

INSERT INTO geek_demo (id, name) 
VALUES (4, 'Mona')
ON DUPLICATE KEY UPDATE name = 'Mona';

Below is the output :

2 row(s) affected

Because a row with id 4 already exists in the geek_demo table, the statement updates the name from Sneha to Mona.
Reading data :

SELECT id, name
FROM geek_demo;

Output :

id name
1 Neha
2 Nisha
3 Sara
4 Mona

Previous Article
Next Article

Similar Reads

update conflicts with concurrent update
In this article, we will discuss the overview of transaction, deadlock and will focus on update conflicts with the concurrent update and understand with the help of an example. Let's discuss it one by one. Transaction :A transaction is a logical unit of work that can have one or more SQL statements. A transaction ends when either you commit it or r
3 min read
How to Update Multiple Columns in Single Update Statement in SQL?
In this article, we will see, how to update multiple columns in a single statement in SQL. We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required. UPDATE for Multiple ColumnsSyntax: U
3 min read
Performing Database Operations in Java | SQL CREATE, INSERT, UPDATE, DELETE and SELECT
In this article, we will be learning about how to do basic database operations using JDBC (Java Database Connectivity) API in Java programming language. These basic operations are INSERT, SELECT, UPDATE, and DELETE statements in SQL language. Although the target database system is Oracle Database, the same techniques can be applied to other databas
6 min read
PHP | MySQL UPDATE Query
The MySQL UPDATE query is used to update existing records in a table in a MySQL database. It can be used to update one or more field at the same time. It can be used to specify any condition using the WHERE clause. Syntax : The basic syntax of the Update Query is - Implementation of Where Update Query : Let us consider the following table "Data" wi
2 min read
How to Update Current Timestamp in MySQL?
MySQL is an easy-to-use RDBMS. Many organizations prefer to use it because of its easy maintainability, easier to prepare schemas, stored procedures, triggers, and database maintenance. In this article, let us see how to update to Current Timestamp in MySQL. Step 1: Database creation Firstly we create the database.Here GEEKSFORGEEKS is the db name.
3 min read
Java Servlet and JDBC Example | Insert data in MySQL
Prerequisites: Servlet, JDBC Connectivity To start with interfacing Java Servlet Program with JDBC Connection: Proper JDBC Environment should set-up along with database creation. To do so, download the mysql-connector.jar file from the internet, As it is downloaded, move the jar file to the apache-tomcat server folder, Place the file in lib folder
4 min read
INSERT() function in MySQL
INSERT() : This function in MySQL is used for inserting a string within a string, removing a number of characters from the original string. Syntax : INSERT(str, pos, len, newstr) Parameters : This method accepts four parameter. str - Original string in which we want to insert another string. pos - The position where we want to insert another string
2 min read
CRUD Operation in MySQL Using PHP, Volley Android - Insert Data
It is known that we can use MySQL to use Structure Query Language to store the data in the form of RDBMS. SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease, and flexibility of use. The application is used for a wide range of purposes, includ
10 min read
How to Insert JSON data into MySQL database using PHP ?
In this article, we are going to see how to store JSON data into MySQL database using PHP through the XAMPP server. Requirements: XAMPP Server Introduction: PHP is an open-source scripting language used to connect with databases and servers. MySQL is a database query language that is used to manage the data in the database to communicate with PHP.
3 min read
MySQL | Common MySQL Queries
MySQL server is a open-source relational database management system which is a major support for web based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly Facebook, Twitter, and Google depends on MySQL dat
9 min read
Article Tags :