MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Insert on Duplicate Key Update
The INSERT INTO statement in MySQL is used to insert new records into a specific table.
MySQL Insert on Duplicate Key Update Statement
When we are trying to insert a new row into a MySQL table column with a UNIQUE INDEX or PRIMARY KEY, MySQL will issue an error, if the value being inserted already exists in the column. This will happen because these constraints require unique values, and duplicate values are not allowed.
However, if we use the MySQL ON DUPLICATE KEY UPDATE clause with with the INSERT INTO statement, MySQL will update the existing rows with the new values instead of showing an error.
Syntax
Following is the basic syntax of ON DUPLICATE KEY UPDATE clause in MySQL 
INSERT INTO my_table (col1, col2, ...) 
VALUES (val1, val2), (val3, val4), ...
ON DUPLICATE KEY UPDATE <col1>=<val1>, <col2>=<val2>,...;
Example
First of all, let us create a table named CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int NOT NULL,
   ADDRESS char (25),
   SALARY decimal (18, 2),
   PRIMARY KEY (ID)
);
Here, we are inserting some records into the above-created table using the INSERT INTO statement as shown below 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 );
Execute the following query to display the records present in the above created CUSTOMERS table 
SELECT * FROM CUSTOMERS;
Following are the records in CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
Here, we are inserting another row into the CUSTOMERS table with an ID value 3 using the INSERT INTO statement 
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) 
VALUES (3, 'Chaitali', 25, 'Mumbai', 6500.00);
As a result, MySQL will issue an error because we are inserting a duplicate ID value 
ERROR 1062 (23000): Duplicate entry '3' for key 'customers.PRIMARY'
We can avoid the above error and update the existing row with the new information using the ON DUPLICATE KEY UPDATE clause along with INSERT INTO statement as shown below 
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) 
VALUES (3, 'Chaitali', 25, 'Mumbai', 6500.00)
ON DUPLICATE KEY UPDATE NAME = "Chaitali",
AGE = 25,
ADDRESS = "Mumbai",
SALARY = 6500.00;
Output
As we can see in the output, the above query updated the existing row in the CUSTOMERS table. As a result, it returns two affected-rows.
Query OK, 2 rows affected (0.01 sec)
Verification
Execute the following query to verify whether the existing row got updated with new information or not 
SELECT * FROM CUSTOMERS;
As we observe the third row in the table, the records got updated.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Chaitali
25
Mumbai
6500.00
Example
In the following query, we are trying to insert a new row into the CUSTOMERS table using the INSERT INTO statement along with the ON DUPLICATE KEY UPDATE clause 
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) 
VALUES (4, 'Hardik', 27, 'Bhopal', 8500.00)
ON DUPLICATE KEY UPDATE NAME = "Hardik",
AGE = 27,
ADDRESS = "Bhopal",
SALARY = 8500.00;
Output
As we can see in the output, there is no conflict occurred while inserting the new row. As a result, it returns one affected-row.
Query OK, 1 row affected (0.01 sec)
Verification
We can verify whether the new row is inserted in the CUSTOMERS table or not using the following query 
SELECT * FROM CUSTOMERS;
As we observe the output below, the new row has been inserted.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Chaitali
25
Mumbai
6500.00
4
Hardik
27
Bhopal
8500.00
INSERT or UPDATE multiple records at once
While inserting or updating multiple records at the same time in MySQL, the value to set for each column may vary depending on which record or records have a conflict.
For example, if we are trying to insert four new rows, but the third has an ID column that conflicts with an existing record, we most likely want to update the existing row based on the data you had in mind for the third row.
Example
Before we perform the next operation, let's look into the records of updated CUSTOMERS table 
SELECT * FROM CUSTOMERS;
Following is the updated CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Chaitali
25
Mumbai
6500.00
4
Hardik
27
Bhopal
8500.00
The following query adds two new rows into the CUSTOMERS table 
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (5, "Komal", 22, "Hyderabad", 4500.00),
(4, "Kaushik", 23, "Kota", 2000.00)
ON DUPLICATE KEY UPDATE 
NAME = VALUES(NAME), 
AGE = VALUES(AGE), 
ADDRESS = VALUES(ADDRESS), 
SALARY = VALUES(SALARY);
Output
As we can see in the output, there are two new rows (ID 5, and 4) and one updated row (ID 4) where it conflicated with an existing row (there is already a row with an ID of "4").
Query OK, 3 rows affected, 4 warnings (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 4
Verification
Execute the following query to verify whether the records have been inserted into the CUSTOMERS table.
SELECT * FROM CUSTOMERS;
If we look at the "CUSTOMERS" table below, we can see that the two new rows added as expected and the values of the conflicted rows have been updated with the new information.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Chaitali
25
Mumbai
6500.00
4
Kaushik
23
Kota
2000.00
5
Komal
22
Hyderabad
4500.00
Client Program
In addition to perform the Insert On Duplicate key Update Query in MySQL table using MySQL query, we can also perform the same operation on a table using a client program.
Syntax
Following are the syntaxes of this operation in various programming languages 
PHPNodeJSJavaPython
To update a duplicate row with new one in MySQL table through a PHP program, we use the DUPLICATE KEY UPDATE along with INSERT statement using the mysqli function query() as 
$sql = "INSERT INTO my_table (column1, column2, ...) 
VALUES (value1, value2), (value3, value4), ... 
ON DUPLICATE KEY UPDATE
 column1 = value1,
 column2 = value2, ..."; 
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$q = "SELECT * FROM tutorials_tbl";
if($res = $mysqli->query($q)){
   printf("Select query executed successfully..!\n");
   printf("The table 'tutorials_tbl' records before insert into duplicate key update query executed: \n");
   while($r = mysqli_fetch_array($res)){
       print_r ($r);
   }
}
$sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(2, 'PHP Tut', 'unknown2', '2023-08-12') ON DUPLICATE KEY UPDATE tutorial_author = 'New Author'";
if($result = $mysqli->query($sql)){
   printf("Insert on Duplicate Key Update query executed successfully..! \n");
}
$q = "SELECT * FROM tutorials_tbl";
if($res = $mysqli->query($q)){
   printf("Select query executed successfully..!\n");
   printf("The table 'tutorials_tbl' records after insert into duplicate key update query executed: \n");
   while($r = mysqli_fetch_array($res)){
       print_r ($r);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
Select query executed successfully..!
The table 'tutorials_tbl' records before insert into duplicate key update query executed:
Array
(
    [0] => 1
    [tutorial_id] => 1
    [1] => Java Tutorial
    [tutorial_title] => Java Tutorial
    [2] => new_author
    [tutorial_author] => new_author
    [3] =>
    [submission_date] =>
)
Array
(
    [0] => 2
    [tutorial_id] => 2
    [1] => PHP Tut
    [tutorial_title] => PHP Tut
    [2] => unknown2
    [tutorial_author] => unknown2
    [3] => 2023-08-12
    [submission_date] => 2023-08-12
)
Insert on Duplicate Key Update query executed successfully..!
Select query executed successfully..!
The table 'tutorials_tbl' records after insert into duplicate key update query executed:
Array
(
    [0] => 1
    [tutorial_id] => 1
    [1] => Java Tutorial
    [tutorial_title] => Java Tutorial
    [2] => new_author
    [tutorial_author] => new_author
    [3] =>
    [submission_date] =>
)
Array
(
    [0] => 2
    [tutorial_id] => 2
    [1] => PHP Tut
    [tutorial_title] => PHP Tut
    [2] => New Author
    [tutorial_author] => New Author
    [3] => 2023-08-12
    [submission_date] => 2023-08-12
)  
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Insert on Duplicate Key Update

MySQL Insert on Duplicate Key Update Statement

Syntax

Example

Output

Verification

Example

Output

Verification

INSERT or UPDATE multiple records at once

Example

Output

Verification

Client Program

Syntax

Example

Output