MariaDB on Duplicate Key Update

In this MariaDB tutorial, we will study the use of the MariaDB on duplicate key update statements and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB on duplicate key update
• MariaDB on duplicate key update multiple value
• MariaDB on duplicate key update not working
• MariaDB on duplicate key update all columns
• MariaDB on duplicate key update auto_increment
• MariaDB on duplicate key update select
• MariaDB insert on duplicate key update multiple values
• MariaDB insert select on duplicate key update

MariaDB on Duplicate Key Update

We’ll learn how to utilize a MariaDB duplicate key update statement to update data if a duplicate key exists in the UNIQUE or PRIMARY KEY indexes in this section.

The MariaDB DUPLICATE KEY UPDATE syntax is as follows:

Insert into Table_name(column_list)
values( value_list)
on duplicate key update column_name=new_value;

First, let’s visit the MARVEL_CHARACTERS table by using the SELECT statement:

SELECT * from MARVEL_CHARACTERS;

As we see in the above image, there are 10 rows in the marvel_characters table. After insertion of one more into marvel_characters table.

INSERT INTO MARVEL_CHARACTERS(MARVEL_ID, FIRST_NAME,LAST_NAME,ACTOR_NAME)
VALUES(211,'GAMORA','','Zoe Saldana')
on duplicate key update marvel_id=211,first_name='GAMORA',
last_name='',actor_name='Zoe Saldana';

Because if there is no identical value, MariaDB will insert a new row in the marvel_characters table. The above statement has the same method as the following methods shown:

INSERT INTO MARVEL_CHARACTERS(FIRST_NAME,LAST_NAME,ACTOR_NAME)
VALUES('GAMORA','','Zoe Saldana');

Now let’s enter a duplicate value in the marvel_id column:

INSERT INTO marvel_characters(marvel_id,FIRST_NAME,LAST_NAME,ACTOR_NAME) 
VALUES 
(211,'GAMORA','','Zoe Saldana') 
ON DUPLICATE KEY UPDATE first_name='drax',
last_name='the destroyer',
actor_name='Dave Batista';

MariaDB shows the following output message:

/* Affected rows: 2  Found rows: 0  Warnings: 0  Duration for 1 query: 0.079 sec. */

• As we see, the marvel_id column already existed with 211 rows in the Marvel_characters table.
• So, by using the DUPLICATE KEY UPDATE statement, it has changed the first_name column and last_name column name from GAMORA to Dave The Destroyer.
• And it will also change the actor_name column name from Zoe Saldana to Dave Batista.

Also, check: MariaDB Window functions

MariaDB on Duplicate Key Update Multiple Value

In this section, we will update duplicate key multiple values by using the DUPLICATE KEY UPDATE statement in MariaDB.

First, let’s see the DC_CHARACTERS table by using the SELECT statement:

SELECT * FROM dc_characters;

As we see in the above image, there are 6 rows in the dc_characters table. After insertion of one more into the dc_characters table.

INSERT INTO DC_CHARACTERS(DC_ID, FIRST_NAME,LAST_NAME,ACTOR_NAME)
VALUES(106,'STEPPENWOLF','The Destroyer','Ciaran Hinds')
on duplicate key update dc_id=106,first_name='STEPPENWOLF',
last_name='The Destroyer',actor_name='Ciaran Hinds';

INSERT INTO DC_CHARACTERS(DC_ID,FIRST_NAME,LAST_NAME,ACTOR_NAME)
VALUES(105,'Barry,'Allen','Ezra Miller')
on duplicate key update dc_id=105,first_name='Barry',
last_name='Allen',actor_name='Ezra Miller';

Because there is no duplicate value, MariaDB will insert multiple rows in the dc_characters table. The above statement has the same method as the following methods shown:

INSERT INTO DC_CHARACTERS(first_name,last_name,actor_name)
values('Steppenwolf','The Destroyer','Ciaran Hinds'),
('Barry','Allen','Ezra Miller');

Now let’s enter a duplicate value in the dc_id column:

INSERT INTO dc_characters(dc_id,FIRST_NAME,LAST_NAME,ACTOR_NAME) 
VALUES 
(106,'STEPPENWOLF','The Destroyer','Ciaran Hinds') 
ON DUPLICATE KEY UPDATE first_name='THE joker',
last_name='',
actor_name='Heath Ledger';

INSERT INTO dc_characters(dc_id,FIRST_NAME,LAST_NAME,ACTOR_NAME) 
VALUES 
(105,'Barry','Allen','Ezra Miller') 
ON DUPLICATE KEY UPDATE first_name='Eobard',
last_name='Thwane',
actor_name='Tom Cavanagh';

MariaDB shows the following output message:

/* Affected rows: 2  Found rows: 0  Warnings: 0  Duration for 1 query: 0.031 sec. */

As we see, the dc_id column already existed with 211 rows in the Marvel_characters table. So by using the DUPLICATE KEY UPDATE statement it has changed the first_name column and last_name column name from STEPPENWOLF to THE JOKER and actor_name column name from Ciaran Hinds to Heath Ledger.

And also for another row with the same process as it changed Barry Allen to Eobard Thwane and the actor_name from Ezra Miller to Tom Canvangh.

Read: MariaDB Row_Number tutorial

MariaDB on Duplicate Key Update Not Working

If a duplicate key update statement in MariaDB fails, the table must have a unique key or primary key constraint. The ON DUPLICATE KEY UPDATE statement is only executed if there is a key conflict when entering the data.

Just add the PRIMARY KEY constraint into the table.

ALTER TABLE_NAME ADD COL_NAME PRIMARY KEY;

It will only do an update if it finds a duplicate key in the table. If it uses the INSERT command for the insertion, that means there are no duplicate keys in the table.

First, let’s create a table USA_BasketballTeam to store basket player names of the United State of America.

CREATE TABLE USA_basketballteam(
player_id INT AUTO_INCREMENT PRIMARY KEY,
player_name VARCHAR(50), 
Current_Age INT,
NBA_team VARCHAR(50),
school VARCHAR(40));

Next, insert rows in the USA_basketballteam by using the INSERT statement:

INSERT INTO usa_basketballteam
(player_name,current_age,NBA_team,school)
VALUES ('Saddiq Bey',22,'Detroit Piston','Villenova University'),
('Miles Bridges',23,'Charlotte Hornets','Michigan State University'),
('Abthony Edwards',20,'Minnisota Timberwolves','University of Gerogia'),
('Darius Garland',22,'Cleveland Cavaliers','Vanderbilt University');

Then query the data by using the SELECT statement to verify the INSERT statement:

SELECT * FROM usa_basketballteam;

Now, we have 4 rows in the USA_basketballteam table. After that, insert one more into the USA_basketballteam table.

INSERT INTO usa_basketballteam(player_name,current_age,nba_team,school) 
VALUES 
('Tyrese Hailburton',21,'Scaremento Kings','Lowa State University') 
  ON DUPLICATE KEY UPDATE 
player_name='Tyrese Hailburton',CURRENT_age=21,nba_team='Scaremento Kings',school='Lowa State University';

There are no duplicate values in the MariaDB so, it inserted a new row in the USA_basketballteam by using the INSERT statement. The above statement has the same effect as shown in the below statement:

INSERT INTO usa_basketballteam(player_name,current_age,nba_team,school)
values('Tyrese Hailburton',21,'Scaremento Kings','Lowa State University');

Now let’s put a duplicate value in the player_id column of the usa_basketbalteam table:

INSERT INTO usa_basketballteam(player_id,player_name,current_age,nba_team,school)
values(7,'Tyrese Hailburton',21,'Scaremento Kings','Lowa State University')
on duplicate key update player_name='Tyle Herro',current_age=22,nba_team='Miami Heat',school='University of Kentucky';

• As we see in the above statement, a row with player_id as 7 already existed in the usa_basketballteam.
• So, the statement updated the player_name column from Tyrese Hailurton to Tyle Herro, current_age column from 21 to 22, nba_team column from Scaremento Kings to Miami Heat.
• And it also updates the school column from Lowa State University to the University of Kentucky.

Read: MariaDB Date Function

MariaDB on Duplicate Key Update All Columns

In this section, we’ll look at how to update all columns in MariaDB using the DUPLICATE KEY UPDATE statement. We’ll need to use the INSERT statement for this. When we use the Enter statement to insert duplicate values into the UNIQUE or PRIMARY KEY index, we get the duplicate key error.

First, let’s see the usa_device table by the SELECT statement:

SELECT * FROM USA_DEVICE;

Now, update all columns by the DUPLICATE KEY UPDATE statement by using the INSERT statement which is shown below:

INSERT INTO usa_device(ID,DEVICE_NAME) 
VALUES(1,'Hathway F1')
ON DUPLICATE KEY UPDATE device_name='ACT';

INSERT INTO usa_Device(ID,Device_Name)
values(2,'Bing 1')
ON DUPLICATE KEY UPDATE device_name='Verzion';

INSERT INTO USA_DEVICE(id,device_name)
values(3,'GitHub 2')
ON DUPLICATE KEY UPDATE device_name='AT & T Internet');

INSERT INTO USA_DEVICE(ID,device_name)
values(4,'Laptop')
ON DUPLICATE KEY UPDATE device_name='T-Mobile'; 

As we see in all the above queries, we have existed rows in a synchronized way in ascending order as 1,2,3,4 already existed in the usa_device table. So, it updated the statement for all the rows of device_name column from Hathway 1 to ACT, Bing 1 to Verzion, GitHub 2 to AT & T Internet, and Laptop to T-Mobile.

Also, check: MariaDB Primary Key With Examples

MariaDB on Duplicate Key Update Auto_Increment

In MariaDB, it attempts to do insert first. This is when player_id gets autoincremented. So, the auto_increment is done, it stays. Then the duplicate is detected and the update happens due to which the value is missed.

As a result, we shouldn’t rely on MariaDB auto_increment to fill up gaps. If there is a necessity, the update and insert overhead is substantially higher. Calculating the incremental value as output is a preferable solution.

let’s see the USA_basketball table first by using the SELECT statement:

SELECT * FROM USA_BASKETBALL;

To do the auto_increment, we will use the auto_increment clause of the player_id column in the usa_basketball table by using the INSERT statement.

SET @NEW_AI = (SELECT MAX(player_id)+1 FROM usa_basketball);
SET @ALTER_SQL = CONCAT('ALTER TABLE `usa_basketball` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL; 
INSERT INTO usa_basketball (player_id,player_name,current_age,nba_team,school) 
VALUES(7,'P.J Washington',23,'Charlotte Hornets','University of Kentucky')
ON DUPLICATE KEY UPDATE player_name=' Cameron Reyonlds',current_age=26,
nba_team='Houston Rockets',school='Tulane University ';

As we see in the above query, the player_id as 7 already existed in the usa_basketball table.

So, it updated all the details of player_id as 7 with that rows and it changed the player_name from P.J Washington to Cameron Reynolds, current__age from 23 to 26, nba_team name from Charlotte Hornets to Houston Rockets, and school name from University of Kentucky to Tulane University.

Read: MariaDB Insert Into + Examples

MariaDB on Duplicate Key Update Select

In MariaDB, the ON DUPLICATE KEY UPDATE statement works only when there we try to insert a row by the INSERT statement that causes a duplicate in the UNIQUE index or PRIMARY KEY index, it will give an error.

First, let’s see a usa_basketball table by using the SELECT statement:

SELECT * FROM USA_BASKETBALL;

Next, update the player_id as 9 by the ON DUPLICATE KEY UPDATE statement by using the INSERT statement which is shown below:

INSERT INTO usa_basketball(player_id,player_name,current_Age,nba_team,school)
VALUES(9,'Isaiah Stweart',20,'Detroit Pistons','Univeristy of Washington')
ON DUPLICATE KEY UPDATE player_name=' Obi Tobbin',current_Age=23,
nba_team='New York Knicks',school='University of Dayton';

MariaDB provides the following message as output which is given below:

/* Affected rows: 2  Found rows: 0  Warnings: 0  Duration for 1 query: 0.078 sec. */

As we see in the above query, the player_id as 9 already existed in the usa_basketball table.

So, it updated all the details of player_id as 9 with that rows and it changed the player_name from Isaiah Stewart to Obi Tobbin, current__age from 20 to 23, nba_team name from Detroit Pistons to New York Knicks, and school name from University of Washington to University of Dayton.

Read: How to Change Column in MariaDB

MariaDB Insert on Duplicate Key Update Mulitple Values

In MariaDB, we will understand how to use MariaDB duplicate key update statements to update data if a duplicate key is INDEX KEY or PRIMARY KEY errors occur when we try to insert a row into a table.

MariaDB’s enhancement to the regular INSERT statement is the INSERT ON DUPLICATE KEY UPDATE statement. Whenever we insert a row into a table if the rows cause a duplicate in the UNIQUE index or PRIMARY KEY index, it will give an error.

The syntax of the INSERT ON DUPLICATE KEY UPDATE is given below:

INSERT INTO TABLE_NAME (COLUMN_LIST)
VALUES (VALUE_LIST) 
ON DUPLICATE KEY UPDATE
current_value1=new_value1,
current_value2=new_value2,
current_value3=new_value3
......;

The only expansion to the INSERT statement is the DUPLICATE KEY UPDATE clause where we have a specified list of columns for value undertaking in the subject of any duplicate arrives.

Normally first, we try to insert duplicate values into the table. If a duplicate value occurs, we will update the current row by the value assigned in the DUPLICATE KEY UPDATE clause.

MariaDB returns the number of simulated rows based on the functions:

• If a new row is introduced, it will influence one row.
• Two rows will be affected if the current row is updated.
• No other rows will be affected if the current row is updated with the current value.

In the DUPLICATE KEY UPDATE statement, we’ll use the VALUE() function procedure to use the values from the INSERT statement, as illustrated below:

INSERT INTO TABLE_NAME(c1) 
VALUES(c1) 
ON DUPLICATE KEY UPDATE c1=VALUE (c1)+1;

If there is a duplicate value in the MariaDB INDEX or PRIMARY KEY, the value of c1 is set to the current value of the expression VALUE(c1) + 1.

The sample example of the DUPLICATE KEY UPDATE statement is given below:

First, let create a table USA_Device to store network devices.

CREATE TABLE USA_Device (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Device_name VARCHAR(50)
);

Next, we will insert rows into the USA_Device table:

INSERT INTO USA_Device(Device_name)
VALUES('Hathway F1'),
('Bing 1'),
('GitHub 2');

Now, let’s verify the INSERT statement of the USA_Device table by using the SELECT statement:

SELECT ID,NAME FROM USA_Device;

As we see in the above image we have 3 rows in the USA_Device table.

Now, let’s enter one more new row into the USA_Device table.

INSERT INTO USA_DEVICE (DEVICE_NAME) 
VALUES ('PRINTER')
ON DUPLICATE KEY UPDATE DEVICE_NAME='PRINTER'; 

As we know, there is no duplicate value of the device_name column as a printer. So, MariaDB inserted a new row in the USA_Device table. The above statement has the following results.

Ultimately, insert a new row with a duplicate value in the ID column of the USA_Device table.

INSERT INTO USA_Device(id,device_name)
values(4,'Printer')
on duplicate key update device_name='Laptop';

MariaDB issues the following messages:

/* Affected rows: 2  Found rows: 0  Warnings: 0  Duration for 1 query: 0.281 sec. */

Now, the row in ID column as 4 exists with the USA_Device table, the statement updates the name from PRINTER to LAPTOP.

Read: MariaDB Update Statement with Examples

MariaDB Insert Select on Duplicate Key Update

The INSERT ON DUPLICATE KEY UPDATE is an extension of MariaDB to the INSERT statement. If there is a duplicate key or primary key value in the table it will perform UPDATE.

First, let’s create a table USA_Basketball to store basketball player’s detail.

create table usa_Basketball(
player_id INT AUTO_INCREMENT PRIMARY KEY,
player_name VARCHAR(50), 
Current_Age INT,
NBA_team VARCHAR(50),
school VARCHAR(40));

Then, let’s do some insertion into the usa_basketball table by using the INSERT statement.

insert into usa_basketball(player_name,current_age,nba_team,school)
values('John Jenkins',30,'Bilbao Basket(Italy)','Vanderbilt University'),
('Keldon Johnson',22,'San Antonio Spurs','University of Kentucky'),
('Josh Magette',32,'Darussafaka Tekfen (Turkey)','University of Alabama
Huntsville'),
('Dakota Mathias',26,'Philadelphia 76ers','Purdue University'),
('Immanuel Quickley',22,'New York Knicks','university of Kentucky'),
('Naz Reid',22,'','LSU');

Then query the data from the usa_basketball table to verify the insert by using the SELECT statement:

SELECT * FROM USA_BASKETBALL;

As we see in the above image, we have inserted 6 rows in the usa_basketball table by using the INSERT statement. After that insert one more row in the usa_basetball table.

INSERT INTO USA_BASKETBALL (player_name,current_age,nba_team,school)
VALUES('Cameron Reynolds',26,'Houston Rockets','Tulane University');

Finally, let’s insert a duplicate value in the Usa_basketball table in the player_id column:

INSERT INTO USA_BASKETBALL(player_id,player_name,current_age,nba_team,school)
values(9,'Cameron Reynolds',26,'Houston Rockets','Tulane University')
on duplicate key update player_id=9,player_name='Isaiah Stweart',current_age=20,nba_team='Detroit Pistons',school='Univeristy of Washington';

• As we see in the above query, the player_id as 9 already existed in the usa_basketball table.
• So, it will update the rows of player_id from 9 and changed the player_name from Cameron Reynolds to Isaiah Stweart, current_age from 26 to 20.
• Also, it will update nba_team from Houston Rockets to Detroit Pistons and school from Tulane University to University of Washington.

Also, take a look at some more MariaDB tutorials.

• MariaDB Reset Root Password
• How to Remove User in MariaDB
• How to Create View in MariaDB
• How to import CSV files in MariaDB

In this tutorial, we have learned about the MariaDB on duplicate key updates using some examples. Moreover, we have also covered the following topics in this tutorial.

• MariaDB on duplicate key update
• MariaDB on duplicate key update multiple value
• MariaDB on duplicate key update not working
• MariaDB on duplicate key update all columns
• MariaDB on duplicate key update auto_increment
• MariaDB on duplicate key update select
• MariaDB insert on duplicate key update multiple values
• MariaDB duplicate key on write or update
• MariaDB insert select on duplicate key update