MySQL Constraints

The MySQL constraints can be used to set certain rules to the column(s) in a table. These constraints can restrict the type of data that can be inserted or updated in a particular column. This helps you to maintain the data accuracy and reliability in a table.

There are two types of MySQL constraints.

• Column level constraints: These type of constraints will only apply to a column in a table.
• Table level constraints: These constraints will apply to the complete table.

The commonly used constraints in MySQL are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, CREATE INDEX, AUTO_INCREMENT, etc.

Syntax

Following is the basic syntax to add a constraint for the column(s) in a table −

CREATE TABLE table_name (
   Column_name1 datatype constraint,
   Column_name2 datatype constraint,
   Column_name3 datatype constraint,
   .........
);

MySQL NOT NULL Constraint

By default, a column in a MySQL table can contain NULL values. In some scenarios, we may want a particular column to not accept or contain NULL values. To do so, we can use the MySQL NOT NULL constraint.

This constraint enforces a specific field to always contain a value, which means that we cannot insert or update a record without adding a value to this field.

Example

In the following query, we are adding the NOT NULL constraint on the ID and NAME columns of the CUSTOMERS table. As a result, the ID and NAME columns will not accept NULL values at the time of record insertion.

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int
);

Let”s try inserting records into this table. The following statement will insert a record into the CUSTOMERS table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Nikhil'', 18);

But, if we try to insert records with NULL values as ID as −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(Null, ''Varun'', 26);

An error will be generated saying “Column ”ID” cannot be null”.

ERROR 1048 (23000): Column ''ID'' cannot be null

In the same way if we try to pass NULLs as values to the NAME column, similar error will be generated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(3, Null, 19);

This will generate the following error −

ERROR 1048 (23000): Column ''NAME'' cannot be null

As we can see in the above queries, the first record is successfully inserted because it does not have null values in the ID and Name columns. Whereas, the second and third records are not inserted because we are trying to insert NULL values in the columns which shouldn”t be NULL.

MySQL UNIQUE Constraint

The UNIQUE constraint in MySQL ensures that every value in a column must be distinct. This means the column with the UNIQUE constraint cannot have the same value repeated; each value must be unique.

Note: We can have one or more UNIQUE constraints on a single table.

Example

The following query creates a UNIQUE constraint on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   UNIQUE (ID)
 );

Now, let us insert the following records into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Nikhil'', 18);
INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Varun'', 26);

In the above code block, the second insert statement returned an error saying “Duplicate entry ”1” for key ”customers.ID” because the ID value we are inserting already exists in the table. Therefore, it is a duplicate and the query generates the following error −

ERROR 1062 (23000): Duplicate entry ''1'' for key ''customers.ID''

MySQL PRIMARY KEY Constraint

The PRIMARY KEY constraint in MySQL is used to uniquely identify each record in a table. This means that, if we define primary key on a particular column in a table, it must contain UNIQUE values, and cannot contain NULL values.

Note: We can have only a single primary key on a table.

Example

The following query creates a PRIMARY KEY on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (ID)
);

Once the table is created, insert the following record into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (1, ''Nikhil'', 18);
Query OK, 1 row affected (0.01 sec)

Since we added the PRIMARY KEY constraint on the ID column, if you try to insert a record with duplicate ID value or NULL value, it will generate an error.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (1, ''Varun'', 26);
ERROR 1062 (23000): Duplicate entry ''1'' for key ''customers.PRIMARY''

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (NULL, ''Datta'', 19);
ERROR 1048 (23000): Column ''ID'' cannot be null

As we can see in the above queries, the first insert statement is successfully inserted into the table. Whereas the second and third statements returned an error because they contain a duplicate and a NULL value in the primary key column i.e. (ID).

MySQL FOREIGN KEY Constraint

The FOREIGN KEY constraint in MySQL is used to link a field or collection of fields in one table to the primary key of another table.

A table with the foreign key is called a child table and the table with the primary key is called the parent table or referenced table.

Example

The following query creates a FOREIGN KEY on the CUST_ID column when the ORDERS table is created −

Table: Customers

CREATE TABLE CUSTOMERS (
   CUST_ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (CUST_ID)
);

Table: Orders

CREATE TABLE ORDERS (
   ORDER_ID int NOT NULL,
   ORDER_NUMBER int NOT NULL,
   CUST_ID int,
   FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS (CUST_ID)
);

MySQL CHECK Constraint

The CHECK constraint in MySQL restricts the range of values that can be inserted into a column. This constraint ensures that the inserted value in a column must be satisfied with the provided condition.

Example

The following query creates a CHECK constraint on the AGE column of the CUSTOMERS table, where it ensures that the age of the student must be 18 or older −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   CHECK (AGE >= 18)
 );

Once the table is created, we can insert the records into the above created table as shown below −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Nikhil'', 18);

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(3, ''Datta'', 19);

Since we added the CHECK constraint on the AGE column such that the age of the student should be equal or greater than 18. If you try to insert a record with age value less than 18, an error will be generated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(2, ''Varun'', 16);
ERROR 3819 (HY000): Check constraint ''customers_chk_1'' is violated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(4, ''Karthik'', 15);
ERROR 3819 (HY000): Check constraint ''customers_chk_1'' is violated.

Example

Here, the following query creates a CHECK constraint on multiple columns (AGE and ADDRESS) −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40),
   CONSTRAINT CHECK_AGE CHECK (AGE >= 18 AND ADDRESS = "Mumbai")
);

Now, let us insert the following records into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(1, ''Nikhil'', 18, ''Mumbai'');
Query OK, 1 row affected (0.01 sec)

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(3, ''Datta'', 19, ''Delhi'');
ERROR 3819 (HY000): Check constraint ''CHECK_AGE_AND_ADDRESS'' is violated.

The second insert statement returned an error because it is violating the condition of the check constraint i.e. (AGE >= 18 AND ADDRESS = “Mumbai”).

MySQL DEFAULT Constraint

The DEFAULT constraint in MySQL is used to assign a default value to a specific column in a table. This default value gets applied to any new records in the DEFAULT specified column when no other value is provided during insertion.

Example

In the following query, we are defining the DEFAULT constraint on the ADDRESS column of the CUSTOMERS table. We assigned “Mumbai” as default value when no value is inserted. −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40) DEFAULT "Mumbai"
);

Here, we are inserting the first two records without any value in the ADDRESS column. In the third record, we are inserting the ADDRESS value as ”Delhi”.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Nikhil'', 18);

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(2, ''Varun'', 16);

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(3, ''Datta'', 19, ''Delhi'');

Exeucte the following query to display the records inserted in the above-created table −

Select * from CUSTOMERS;

In the following output, we can see that the value in the ADDRESS column for the first two rows is by default “Mumbai”.

MySQL CREATE INDEX Constraint

The CREATE INDEX constraint in MySQL is used to create indexes for one more columns in a table.

The indexes are used to fetch the data from the database much quicker. However, the users cannot see the indexes in action, instead, they are just used to speed up the searches and queries.

Example

Here, we are creating a table named CUSTOMERS using the query below −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40),
   PRIMARY KEY (ID)
);

The following query creates an index named “index_address” on the ADDRESS column of the CUSTOMERS table −

CREATE INDEX index_address ON CUSTOMERS (ADDRESS);

MySQL AUTO_INCREMENT Constraint

When a AUTO_INCREMENT constraint is defined on a particular column of a table, it will automatically generate a unique number when a new record is inserted into that column.

By default, the starting value is 1, and it will automatically increment its value by 1 for each new record.

Example

The following query adds an AUTO_INCREMENT constraint on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (ID)
);

In the insert statements below, we are not inserting ID values.

INSERT INTO STUDENTS(NAME, AGE) VALUES(''Nikhil'', 18);
INSERT INTO STUDENTS(NAME, AGE) VALUES(''Varun'', 16);
INSERT INTO STUDENTS(NAME, AGE) VALUES(''Datta'', 19);

Now, execute the following query to display the records of the above-created table −

Select * from CUSTOMERS;

As we can see in the STUDENTS table below, the values in the ID column are automatically incremented because of the AUTO_INCREMENT constraint on the ID column.

Now that we have learned how to create tables in MySQL and insert values into it in the previous tutorials, the next step is to check whether the values are recorded in this table or not. To do this, one must use the SELECT statement to retrieve and view the records in that specific table.”

MySQL Select Statement

The MySQL SELECT command is used to fetch data from the MySQL database in the form of a result table. These result tables are called result-sets.

Note − We can use this command at ”mysql>” prompt as well as in any script like PHP, Node.js, Java, python, etc.

Syntax

Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −

SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

• You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.

• We can fetch one or more fields in a single SELECT command.

• We can specify star (*) in place of fields. In this case, SELECT will return all the fields.

• We can specify any condition using the WHERE clause.

• We can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.

• We can limit the number of returns using the LIMIT attribute.

Fetching Data Using SELECT from Command Prompt

This will use SQL SELECT command to fetch data from an MySQL table.

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)
);

The following query inserts 7 records into the above created table −

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 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

To Retrieve All Fields

Now, to retrieve all the data present in a CUSTOMERS table, we use the following SELECT statement −

SELECT * from CUSTOMERS;

Following are the records present in the CUSTOMERS table −

To Retrieve Selective Fields

Here, we are retrieving only three columns/fields, ID, NAME, and ADDRESS present in the CUSTOMERS table, using the following query −

SELECT ID, NAME, ADDRESS FROM CUSTOMERS;

On executing the given query, the output is displayed as follows −

Computing using SELECT in Command Prompt

The SELECT statement is not only used to fetch data from tables but can also be used to get the results of mathematical computations in a tabular format. In these cases, you don”t have to mention a specific database table in the SELECT statement.

Following is the syntax to do so −

SELECT [math_computation];

Example

In the following example, let us solve a mathematical computation using the SELECT statement −

SELECT 46475*453;

Output

The output for the program query is produced as given below −

Aliasing a Column in SELECT Statement

MySQL database provides a method to alias column names into a more understandable and relative name when being displayed. This is done using the ”AS” keyword. This keyword is used in the SELECT statement as well.

Following is the syntax to do so −

SELECT column_name AS alias_name FROM table_name;

You can also use an alias to display select expressions with the same syntax; you should use a select expression instead of column_name in the syntax.

Example

In the example below, we are retrieving the ID column from the previously created CUSTOMERS table. We aliased the ID column as “Identity_Document” −

SELECT ID AS Identity_Document FROM CUSTOMERS;

As we can see the output, the alias name ”Identity_Document” has been used instead of ”ID”.

Select Query into MySQL Database Using a Client Program

Besides getting data from a table using the SELECT query, you can also use a client program to perform the SELECT operation on a table.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql="SELECT COLUMN_NAME1, COLUMN_NAME2,... FROM TABLE_NAME";
$mysqli->query($sql);

sql="SELECT field1, field2,...fieldN FROM table_name";
con.query(sql);

String sql="SELECT COLUMN_NAME1, COLUMN_NAME2,... FROM TABLE_NAME";
statement.executeQuery(sql);

select_query = "SELECT COLUMN1, COLUMN2,.. FROM TABLE_NAME";
cursorObj.execute(select_query);

Example

Following are the programs −

$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.
'');
$sql = "SELECT * FROM tutorials_tbl";
if($result = $mysqli->query($sql)){
   printf("Select statement executed successfully..!  ");
   printf("Records are: ");
   while($row = mysqli_fetch_row($result)){
      print_r ($row);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Select statement executed successfully..!  Records are:
Array
(
    [0] => 1
    [1] => MySQL Tut
    [2] => unknown
    [3] => 2023-07-25
)
Array
(
    [0] => 2
    [1] => PHP Tut
    [2] => unknown2
    [3] => 2023-08-12
)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");

  //Creating a Database
  sql = "CREATE DATABASE IF NOT EXISTS TUTORIALS"
  con.query(sql);

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating a Table
  sql = "CREATE TABLE IF NOT EXISTS tutorials_tbl(tutorial_id INT NOT NULL PRIMARY KEY, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE)"
  con.query(sql);
  //Inserting records into table
  sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(1, ''Learn PHP'', ''John Paul'', NOW()), (2, ''Learn MySQL'', ''Abdul S'', NOW()), (3, ''JAVA Tutorial'', ''Sanjay'', ''2007-05-06''), (4, ''Python Tutorial'', ''Sasha Lee'', ''2016-09-04''), (5, ''Hadoop Tutorial'', ''Chris Welsh'', NOW())"
  con.query(sql);

  //Selecting all the records from table
  sql = "SELECT * FROM tutorials_tbl"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

Connected!
[
  {
    tutorial_id: 1,
    tutorial_title: ''Learn PHP'',
    tutorial_author: ''John Paul'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 2,
    tutorial_title: ''Learn MySQL'',
    tutorial_author: ''Abdul S'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''JAVA Tutorial'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: ''Python Tutorial'',
    tutorial_author: ''Sasha Lee'',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: ''Hadoop Tutorial'',
    tutorial_author: ''Chris Welsh'',
    submission_date: 2023-07-25T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SelectQuery {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM tutorials_tbl";
            rs = st.executeQuery(sql);
            System.out.println("Select query executed successfully..!");
            System.out.println("Table records: ");
            while(rs.next()) {
            	String tutorial_id = rs.getString("tutorial_id");
            	String tutorial_title = rs.getString("tutorial_title");
            	String tutorial_author = rs.getString("tutorial_author");
            	String submission_date = rs.getString("submission_date");
            	System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Select query executed successfully..!
Table records:
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08

import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl''
#Creating a cursor object
cursorObj = connection.cursor()
select_query = f"SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM {table_name}"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Tutorial Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Derived Table Result:
Tutorial Table Data:
(1, ''Learn PHP'', ''John Paul'', datetime.date(2023, 3, 28))
(2, ''Learn MySQL'', ''Abdul S'', datetime.date(2023, 3, 28))
(3, ''JAVA Tutorial'', ''Sanjay'', datetime.date(2007, 5, 6))
(4, ''Python Tutorial'', ''Sasha Lee'', datetime.date(2016, 9, 4))
(5, ''Hadoop Tutorial'', ''Chris Welsh'', datetime.date(2023, 3, 28))
(6, ''R Tutorial'', ''Vaishnav'', datetime.date(2011, 11, 4))

After creating a table in a MySQL database with the CREATE TABLE statement, we will only have an empty table that only has its structure defined. To populate it with data, we need to add records manually using separate queries.

The MySQL INSERT Statement

To insert data into a MySQL table, we would need to use the MySQL INSERT statement. We can insert data into the MySQL table by using the ”mysql>” prompt or by using any client program such as PHP, Java etc.

Since the structure of a table is already defined, the MySQL INSERT statement will only accept the data which is according to the structure of the table. Data inserted into a table must have same data types, satisfy the constraints (if any), etc. If the inserted data does not satisfy these conditions, the INSERT INTO statement displays an error.

Syntax

Following is the syntax of the MySQL INSERT statement −

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

To insert string values, it is required to keep all the values into double or single quotes. For example “value”.

Inserting Data from the Command Prompt

To insert data from the command prompt, we will use SQL INSERT INTO statement to insert data into an MySQL table.

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)
);

Now, we will insert a single record into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 );

We can also insert multiple records simultaneously using the following query −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 );

Inserting records into a database is also possible even if you do not specify the column name if the comma separated values in the query match the attributes of corresponding columns as shown below −

INSERT INTO CUSTOMERS VALUES
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Verification

We can verify whether the the data is inserted using this statement as shown below −

SELECT * FROM CUSTOMERS;

The CUSTOMERS table produced is as shown below −

Inserting Data Into a Table Using Another Table

Sometimes, we just need to copy the data from one existing table in a database to another table in the same database. And there are various ways to do so −

• Using INSERT… SELECT
• Using INSERT… TABLE

INSERT… SELECT Statement

We can populate the data into a table through the select statement over another table; provided the other table has a set of fields, which are required to populate the first table.

Here is the syntax −

INSERT INTO table_name1 [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM table_name2
[WHERE condition];

Example

In the following query, we are creating another table CUSTOMERS_Copy with the same structure as CUSTOMERS table −

CREATE TABLE CUSTOMERS_Copy (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us use the INSERT…INTO statement to insert the records into the CUSTOMERS_Copy table from CUSTOMERS table.

INSERT INTO CUSTOMERS_Copy SELECT * from CUSTOMERS;

Output

This will generate the following output −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

Execute the following query to verify whether the the records are inserted from CUSTOMERS table or not −

SELECT * FROM CUSTOMERS_Copy;

The CUSTOMERS_Copy table obtained is as shown below −

INSERT…TABLE Statement

On the other hand, instead of selecting specific columns, we can insert the contents of one table into another using the INSERT…TABLE statement.

Following is the syntax to do so −

INSERT INTO table1 TABLE table2;

Example

In this example, let us use the same CUSTOMERS table we have created in the previous example and copy its contents to another table CUSTOMERS_dummy.

For that, first of all, we will create the table CUSTOMERS_dummy with the same structure as CUSTOMERS table −

CREATE TABLE CUSTOMERS_dummy (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Using the CUSTOMERS table, we will insert all its values into CUSTOMERS_dummy table −

INSERT INTO CUSTOMERS_dummy TABLE CUSTOMERS;

Output

This query will generate the following output −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

We can retrieve the contents of a table using the SELECT statement. You can verify whether the the data is inserted using this statement as shown below −

SELECT * FROM CUSTOMERS_dummy;

The CUSTOMERS_dummy table obtained is as shown below −

INSERT … SET

You can insert a record by setting values to selected columns using the INSERT…SET statement. Following is the syntax of this statement −

INSERT INTO table_name SET column_name1 = value1, column_name2=value2,......;

Where, table_name is the name of the table into which you need to insert the record and column_name1 = value1, column_name2 = value2 …… are the selected column names and the respective values.

If you are inserting values into a table using the INSERT … SET statement and if you provide values for only a certain columns the values in the remaining will be NULL.

Example

Following query inserts a record into the CUSTOMERS table using the INSERT…SET statement. Here, we are passing values only to the ID, NAME and, AGE columns (remaining values will be NULL) −

INSERT INTO CUSTOMERS
SET ID = 8, NAME = ''Sarmista'', AGE = 35;

Verification

If you retrieve the contents of the CUSTOMERS table using the SELECT statement you can observe the inserted row as shown below

SELECT * FROM CUSTOMERS WHERE ID=8;

Output

Following is the output of the above program −

Inserting Data Using a Client Program

Besides inserting data into a table in a MySQL database with a MySQL query, we can also use a client program to perform the INSERT operation.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "INSERT INTO tutorials_tbl VALUES(1,''Learn MySQL'',''Mahesh'', NOW())";
$mysqli->query($sql);

sql = "INSERT INTO tutorials_tbl VALUES(1, ''Learn PHP'', ''John Paul'', NOW())";
con.query(sql)

String sql = "INSERT INTO tutorials_tbl VALUES (1, ''Learn PHP'', ''John Paul'', NOW());";
st.executeUpdate(sql);

sql = "INSERT INTO tutorials_tbl VALUES (1, ''Learn PHP'', ''John Paul'', ''2023-3-28'')"
cursorObj.execute(sql)

Example

Following are the programs −

$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();
}
$sql = "INSERT INTO tutorials_tbl VALUES(1,''Learn MySQL'',''Mahesh'', NOW())";
if($result = $mysqli->query($sql)){
   printf("Data inserted successfully..!");
 }
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Data inserted successfully..!

var mysql = require(''mysql2'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});
//Connecting to MySQL
con.connect(function (err) {
if (err) throw err;
console.log("Connected!");
//Creating a Database
sql = "CREATE DATABASE IF NOT EXISTS TUTORIALS"
con.query(sql);
//Selecting a Database
sql = "USE TUTORIALS"
con.query(sql);
//Creating a Table
sql = "CREATE TABLE IF NOT EXISTS tutorials_tbl(tutorial_id INT NOT NULL PRIMARY KEY, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE)";
con.query(sql);
//Inserting records into table
sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(1, ''Learn PHP'', ''John Paul'', NOW()), (2, ''Learn MySQL'', ''Abdul S'', NOW()), (3, ''JAVA Tutorial'', ''Sanjay'', ''2007-05-06''), (4, ''Python Tutorial'', ''Sasha Lee'', ''2016-09-04''), (5, ''Hadoop Tutorial'', ''Chris Welsh'', NOW())";
 con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
   });
});

Connected!
-----------------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 5,
  insertId: 0,
  info: ''Records: 5  Duplicates: 0  Warnings: 3'',
  serverStatus: 2,
  warningStatus: 3,
  changedRows: 0
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InsertQuery {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection(url, user, password);
        Statement st = con.createStatement();
        //System.out.println("Database connected successfully...!");
        String sql = "INSERT INTO tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (1, ''Learn PHP'', ''John Paul'', NOW());";
        st.executeUpdate(sql);
        System.out.println("Record insered successfully...!");
        System.out.println("Table records: ");
        String sql1 = "SELECT * FROM tutorials_tbl";
        rs = st.executeQuery(sql1);
        while(rs.next()) {
          String tutorial_id = rs.getString("tutorial_id");
          String tutorial_title = rs.getString("tutorial_title");
          String tutorial_author = rs.getString("tutorial_author");
          String submission_date = rs.getString("submission_date");
          System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
        }
     }catch(Exception e) {
        e.printStackTrace();
     }
   }
}

Record insered successfully...!
Table records:
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date:
2023-08-08

import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl''
new_tutorial_data = [
    (2, ''Learn MySQL'', ''Abdul S'', ''2023-03-28''),
    (3, ''JAVA Tutorial'', ''Sanjay'', ''2007-05-06''),
    (4, ''Python Tutorial'', ''Sasha Lee'', ''2016-09-04''),
    (5, ''Hadoop Tutorial'', ''Chris Welsh'', ''2023-03-28''),
    (6, ''R Tutorial'', ''Vaishnav'', ''2011-11-04'')
]
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute("truncate table tutorials_tbl")
sql = "INSERT INTO tutorials_tbl VALUES (1, ''Learn PHP'', ''John Paul'', ''2023-3-28'')"
cursorObj.execute(sql)
insert_query = f''INSERT INTO {table_name} (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (%s, %s, %s, %s)''
cursorObj.executemany(insert_query, new_tutorial_data)
connection.commit()
print("Row inserted successfully.")
cursorObj.close()
connection.close()

Row inserted successfully.

The MySQL UPDATE Statement

The MySQL UPDATE Query is used to modify the existing records in a table. This statement is a part of Data Manipulation Language in SQL, as it only modifies the data present in a table without affecting the table”s structure.

Since it only interacts with the data of a table, the UPDATE statement needs to used cautiously. If the rows to be modified aren”t selected beforehand, all the rows in the table will be affected and the correct table data is either lost or needs to be reinserted.

Therefore, to filter records that needs to be modified, MySQL always provides a WHERE clause. Using a WHERE clause, you can either update a single row or multiple rows.

The UPDATE statement makes use of locks on each row while modifying them in a table, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query.

Syntax

Following is the SQL syntax of the UPDATE command to modify the data in the MySQL table −

UPDATE table_name SET field1 = new-value1, field2 = new-value2
[WHERE Clause]

• You can update one or more field altogether.
• You can specify any condition using the WHERE clause.
• You can update the values in a single table at a time.

The WHERE clause is very useful when you want to update the selected rows in a table.

Updating Data from the Command Prompt

This will use the SQL UPDATE command with the WHERE clause to update the selected data in an MySQL table.

Example

First of all, let us create a table named CUSTOMERS using the following CREATE 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)
);

The below query inserts 7 records in to the above created table −

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 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to retrieve all the records present in the CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Here, we are using the SQL UPDATE query to update the NAME field in the CUSTOMERS table. It sets the name to ”Nikhilesh” for the row where the ”ID” is equal to 6.

UPDATE CUSTOMERS
SET NAME = ''Nikhilesh''
WHERE ID = 6;

Output

The above query has excuted successfully without any errors.

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify whether the name has replaced to ”Nikhilesh”, use the following query −

Select * from CUSTOMERS;

As we can see the output, the NAME with ID 6 has been updated −

Updating Multiple Records from the Command Prompt

Using UPDATE statement, multiple rows and columns in a MySQL table can also be updated. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it. Thus, only updating the values in those records.

Example

Now, let us update multiple records in the previously created CUSTOMERS table using the following query −

UPDATE CUSTOMERS
SET ADDRESS = ''Vishakapatnam''
WHERE ID = 6 OR ID = 3;

Output

The above query has excuted successfully without any errors.

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

To verify whether the ADDRESS has replaced with ”Vishakapatnam” in ID = 6 and 3, use the following query −

Select * from CUSTOMERS;

As we can see the output, the NAME with ID 6 has been updated −

Updating a table Using a Client Program

In addition to update records in a table using the MySQL query, we can also perform the UPDATE operation on a table using a client program.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql="UPDATE table_name SET field1 = new-value1,
   field2 = new-value2 [WHERE Clause]";
$mysqli->query($sql);

sql = "UPDATE table_name SET column1 = value1,
   column2 = value2, ... WHERE condition";
con.query(sql);

String sql="UPDATE table_name SET field1 = new-value1,
   field2 = new-value2 [WHERE Clause]";
statement.executeUpdate(sql);

update_query = "UPDATE table_name SET field1 = new-value1,
   field2 = new-value2 [WHERE Clause]"
cursorObj.execute(update_query);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''root@123
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf(''Connected successfully.<br />'');

if ($mysqli->query(''UPDATE tutorials_tbl set tutorial_title = "Learning Java" where tutorial_id = 4'')) {
   printf("Table tutorials_tbl updated successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not update table: %s<br />", $mysqli->error);
}

$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";

$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
         $row["tutorial_id"],
         $row["tutorial_title"],
         $row["tutorial_author"],
         $row["submission_date"]);
   }
} else {
   printf(''No record found.<br />'');
}
mysqli_free_result($result);
$mysqli->close();

Connected successfully.
Table tutorials_tbl updated successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 4, Title: Learning Java, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
  });

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Updating a single record in the table
  sql = "UPDATE tutorials_tbl SET tutorial_title = ''Learning Java'' WHERE tutorial_id = 3;"
  con.query(sql);

  //Selecting records from table
  sql = "SELECT * FROM tutorials_tbl"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

Connected!
[
  {
    tutorial_id: 1,
    tutorial_title: ''Learn PHP'',
    tutorial_author: ''John Paul'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 2,
    tutorial_title: ''Learn MySQL'',
    tutorial_author: ''Abdul S'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''Learning Java'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: ''Python Tutorial'',
    tutorial_author: ''Sasha Lee'',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: ''Hadoop Tutorial'',
    tutorial_author: ''Chris Welsh'',
    submission_date: 2023-07-25T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class UpdateQuery {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "UPDATE tutorials_tbl SET tutorial_title = ''Learning Java'' WHERE tutorial_id = 3";
            st.executeUpdate(sql);
            System.out.println("Update query executed successfully..!");
            String sql1 = "SELECT * FROM tutorials_tbl";
            rs = st.executeQuery(sql1);
            System.out.println("Table records: ");
            while(rs.next()) {
            	String tutorial_id = rs.getString("tutorial_id");
            	String tutorial_title = rs.getString("tutorial_title");
            	String tutorial_author = rs.getString("tutorial_author");
            	String submission_date = rs.getString("submission_date");
            	System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Update query executed successfully..!
Table records:
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08
Id: 2, Title: Angular Java, Author: Abdul S, Submission_date: 2023-08-08
Id: 3, Title: Learning Java, Author: Sanjay, Submission_date: 2007-05-06
Id: 4, Title: Python Tutorial, Author: Sasha Lee, Submission_date: 2016-09-04
Id: 5, Title: Hadoop Tutorial, Author: Chris Welsh, Submission_date: 2023-08-08

import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
update_query = "UPDATE tutorials_tbl SET tutorial_title = ''Learning Java'' WHERE tutorial_id = 3"
cursorObj.execute(update_query)
connection.commit()
print("Row updated successfully.")
cursorObj.close()
connection.close()

Row updated successfully.

MySQL DELETE Statement

If we want to delete a record from any MySQL table, then we can use the SQL command DELETE FROM. This statement is a part of Data Manipulation Language in SQL as it interacts with the data in a MySQL table rather than the structure.

DELETE statement can be used to delete multiple rows of a single table and records across multiple tables. However, in order to filter the records to be deleted, we can use the WHERE clause along with the DELETE statement.

We can use this command at the mysql> prompt as well as in any script like PHP, Node.js, Java, and Python.

Syntax

Following is the basic SQL syntax of the DELETE command to delete data from a MySQL table −

DELETE FROM table_name [WHERE Clause]

• If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.

• We can specify any condition using the WHERE clause.

• We can delete records in a single table at a time.

The WHERE clause is very useful when you want to delete selected rows in a table.

Deleting Data from a MySQL Table

We use the MySQL DELETE query to delete records from a database table. However, we can delete single, multiple rows at once or even delete all records from a table in a single query. Let us discuss them one by one futher in this tutorial with appropriate examples.

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)
);

The following query inserts 7 records into the above created table −

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 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the below SELECT statement to retrieve all the records present in the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us delete a single record (ID = 1) from the CUSTOMERS table using the DELETE statement as follows −

DELETE FROM CUSTOMERS WHERE ID = 1;

Output

On executing the given query, the output is displayed as follows −

Query OK, 1 row affected (0.00 sec)

Verification

Execute the following query to verify whether the above record have been deleted or not −

Select * From CUSTOMERS;

As we can see in the output, the row with ID=1 has been deleted −

Deleting Multiple Rows

We can also delete multiple rows using the DELETE statement. For this, we just have to specify multiple conditions in the WHERE clause that are satisfied by all the records that are supposed to be deleted.

Example

Here, we are deleting records from previously created CUSTOMERS table whose ID is 2 and 3 −

DELETE FROM CUSTOMERS WHERE ID = 2 OR ID = 3;

Output

The output for the program above is produced as given below −

Query OK, 2 rows affected (0.01 sec)

Verification

Execute the following query to verify whether the above records have been deleted or not −

Select * From CUSTOMERS;

As we can see in the output, the row with ID values 2 and 3 are deleted −

Deleting All Rows

If we want to delete all records from a MySQL table, simply execute the DELETE statement without using the WHERE clause. Following is the syntax −

DELETE FROM table_name;

Example

The following query will delete all records from the CUSTOMERS table −

DELETE FROM CUSTOMERS;

Output

On executing the given program, the output is displayed as follows −

Query OK, 4 rows affected (0.01 sec)

Verification

Execute the following query to verify whether all the records from CUSTOMERS table have been deleted or not −

Select * From CUSTOMERS;

As we can see the output below, empty set has been returned i.e all the records have been deleted.

Empty set (0.00 sec)

Delete Query in MySQL Using a Client Program

Besides deleting records of a database table with a MySQL query, we can also use a client program to perform the DELETE operation.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql="DELETE FROM table_name [WHERE Clause]";
$mysqli->query($sql);

sql = "DELETE FROM table_name [WHERE Clause]";
VALUES (value1, value2, value3,...valueN)"
con.query(sql);

String sql="DELETE FROM table_name [WHERE Clause]";
statement.executeUpdate(sql);

delete_query = "DELETE FROM table_name [WHERE Clause]"
cursorObj.execute(delete_query);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''root@123
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf(''Connected successfully.<br />'');

if ($mysqli->query(''DELETE FROM tutorials_tbl where tutorial_id = 4'')) {
   printf("Table tutorials_tbl record deleted successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not delete record from table: %s<br />", $mysqli->error);
}
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
   FROM tutorials_tbl";

$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
         $row["tutorial_id"],
         $row["tutorial_title"],
         $row["tutorial_author"],
         $row["submission_date"]);
   }
} else {
   printf(''No record found.<br />'');
}
mysqli_free_result($result);
$mysqli->close();

Connected successfully.
Table tutorials_tbl record deleted successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Updating a single record in the table
  sql = "DELETE FROM tutorials_tbl WHERE tutorial_id = 5;"
  con.query(sql);

  //Selecting records from table
  sql = "SELECT * FROM tutorials_tbl"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

Connected!
[
  {
    tutorial_id: 1,
    tutorial_title: ''Learn PHP'',
    tutorial_author: ''John Paul'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 2,
    tutorial_title: ''Learn MySQL'',
    tutorial_author: ''Abdul S'',
    submission_date: 2021-03-27T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''Learning Java'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: ''Python Tutorial'',
    tutorial_author: ''Sasha Lee'',
    submission_date: 2016-09-03T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DeleteQuery {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "DELETE FROM tutorials_tbl WHERE tutorial_id = 6";
            st.executeUpdate(sql);
            System.out.println("Delete query executed successfully..!");
            String sql1 = "SELECT * FROM tutorials_tbl";
            rs = st.executeQuery(sql1);
            System.out.println("Table records: ");
            while(rs.next()) {
            	String tutorial_id = rs.getString("tutorial_id");
            	String tutorial_title = rs.getString("tutorial_title");
            	String tutorial_author = rs.getString("tutorial_author");
            	String submission_date = rs.getString("submission_date");
            	System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Delete query executed successfully..!
Table records:
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08
Id: 2, Title: Angular Java, Author: Abdul S, Submission_date: 2023-08-08
Id: 3, Title: Learning Java, Author: Sanjay, Submission_date: 2007-05-06
Id: 4, Title: Python Tutorial, Author: Sasha Lee, Submission_date: 2016-09-04
Id: 5, Title: Hadoop Tutorial, Author: Chris Welsh, Submission_date: 2023-08-08

import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
delete_query = "DELETE FROM tutorials_tbl WHERE tutorial_id = 6"
cursorObj.execute(delete_query)
connection.commit()
print("Row deleted successfully.")
cursorObj.close()
connection.close()

Row deleted successfully.

MySQL REPLACE Statement

In general, if we want to add records into an existing table, we use the MySQL INSERT statement. Likewise, we can also add new records or replace an existing records using the MySQL REPLACE statement. The replace statement is similar to the insert statement.

The only difference is, while inserting a record using the insert statement if a existing column has a UNIQUE or PRIMARY KEY constraint, if the new record has same value for this column an error will be generated.

In the case of the REPLACE statement if you try to insert a new column with duplicate value for the column with UNIQUE or PRIMARY KEY constraints the old record will be completely replaced by the new record.

Syntax

Following is the syntax of the MySQL REPLACE statement −

REPLACE INTO table_name (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Where, table_name is the name of the table into which you need to insert data, (column1, column2, column3,…columnN) are the names of the columns and (value1, value2, value3,…valueN) are the values in the record.

Example

Let us start with creating a table with name CUSTOMERS in MySQL database with primary key constraint on the ID column as shown below −

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)
);

The following query adds two records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 );

Execute the following query to display all the records present in the CUSTOMERS table −

select * FROM CUSTOMERS;

Following are the records in CUSTOMERS table −

Now, let us try to insert another record with ID value 2 −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(2, ''Kaushik'', 23, ''Kota'', 2000.00 );

Since the ID column has a primary key constraint, an error will be generated as shown below −

ERROR 1062 (23000): Duplicate entry ''2'' for key ''customers.PRIMARY''

Now, use the REPLACE statement to replace the existing record in the table −

REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(2, ''Kaushik'', 20, ''Kota'', 2000.00 );

Output

Executing the query above will produce the following output −

Query OK, 2 rows affected (0.01 sec)

Verification

Execute the following SELECT statement to verify whether the new record has been replaced or not −

select * from CUSTOMERS;

As we can observe the output below, the existing record has been replaced with the new record −

Inserting records using REPLACE statement

When you use the REPLACE statement to insert a record, if that record doesn”t match any existing records in the table, it will be added as a new record.

Example

The following query uses REPLACE statement to add three new records into the above CUSTOMERS table −

REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(3, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(4, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(5, ''Komal'', 22, ''Hyderabad'', 4500.00 );

Output

Executing the query above will produce the following output −

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Verification

Execute the following query to verify whether the above records has been inserted into CUSTOMERS table or not −

SELECT * FROM CUSTOMERS;

As we can observe the CUSTOMERS below, the above records are inserted as new records into the table.

Replacing a Record Using a Client Program

Besides replacing records of a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPLACE operation.

Syntax

Following are the syntaxes to use REPLACE query in various programming languages −

$sql="REPLACE INTO TABLE_NAME SET COLUMN_NAME1 = NEW_VALUE, COLUMN_NAME2 = NEW_VALUE...";
$mysqli->query($sql);

sql="REPLACE INTO table_name (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN)"
con.query(sql);

String sql="REPLACE INTO TABLE_NAME SET COLUMN_NAME1 = NEW_VALUE, COLUMN_NAME2 = NEW_VALUE...";
statement.executeUpdate(sql);

replace_query = "REPLACE INTO table_name (column1, column2, column3,...columnN)
   VALUES (value1, value2, value3,...valueN)"
cursorObj.execute(replace_query );

Example

Following are the programs −

$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.
'');
$sql = "REPLACE INTO tutorials_tbl SET tutorial_id = 1, tutorial_title = ''Java Tutorial'', tutorial_author = ''new_author''";
if($result = $mysqli->query($sql)){
   printf("Replace statement executed successfully..!  ");
}
$q = "SELECT * FROM tutorials_tbl";
if($res = $mysqli->query($q)){
printf("Records after replace statement are: ");
   while($row = mysqli_fetch_row($res)){
      print_r ($row);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Replace statement executed successfully..!  Records after replace statement are: Array
(
    [0] => 1
    [1] => Java Tutorial
    [2] => new_author
    [3] =>
)
Array
(
    [0] => 2
    [1] => PHP Tut
    [2] => unknown2
    [3] => 2023-08-12
)

var mysql = require(''mysql2'');

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating a table
  sql = "CREATE TABLE sales(ID INT UNIQUE, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));"
  con.query(sql);

  //Inserting records into table
  sql = "INSERT into sales values(1, ''Mouse'', ''Puja'', DATE(''2019-03-01''), TIME(''10:59:59''), 3000, ''Vijayawada'');"
  con.query(sql);

  //Displaying records before replacing
  sql = "Select * from sales"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
      console.log("************************************************")
  });

  //Replacing the record
  sql = "REPLACE into sales values(1, ''Mobile'', ''Vanaja'', DATE(''2019-03-01''), TIME(''10:10:52''), 9000, ''Chennai'');"
  con.query(sql);

  //Displaying records after replacing
  sql = "Select * from sales"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

Connected!
[
  {
    ID: 1,
    ProductName: ''Mouse'',
    CustomerName: ''Puja'',
    DispatchDate: 2019-02-28T18:30:00.000Z,
    DeliveryTime: ''10:59:59'',
    Price: 3000,
    Location: ''Vijayawada''
  }
]
************************************************
[
  {
    ID: 1,
    ProductName: ''Mobile'',
    CustomerName: ''Vanaja'',
    DispatchDate: 2019-02-28T18:30:00.000Z,
    DeliveryTime: ''10:10:52'',
    Price: 9000,
    Location: ''Chennai''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ReplaceQuery {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "REPLACE INTO tutorials_tbl SET tutorial_id = 1, tutorial_title = ''Java Tutorial'', tutorial_author = ''John Smith''";
            st.executeUpdate(sql);
            System.out.println("Replace query executed successfully..!");
            String sql1 = "SELECT * FROM tutorials_tbl";
            rs = st.executeQuery(sql1);
            System.out.println("Table records: ");
            while(rs.next()) {
            	String tutorial_id = rs.getString("tutorial_id");
            	String tutorial_title = rs.getString("tutorial_title");
            	String tutorial_author = rs.getString("tutorial_author");
            	String submission_date = rs.getString("submission_date");
            	System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Replace query executed successfully..!
Table records:
Id: 1, Title: Java Tutorial, Author: John Smith, Submission_date: null
Id: 2, Title: Angular Java, Author: Abdul S, Submission_date: 2023-08-08
Id: 3, Title: Learning Java, Author: Sanjay, Submission_date: 2007-05-06
Id: 4, Title: Python Tutorial, Author: Sasha Lee, Submission_date: 2016-09-04
Id: 5, Title: Hadoop Tutorial, Author: Chris Welsh, Submission_date: 2023-08-08

import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut'')
#Creating a cursor object
cursorObj = connection.cursor()
replace_query = "REPLACE INTO tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (3, ''Learning Java'', ''John Doe'', ''2023-07-28'')"
cursorObj.execute(replace_query)
connection.commit()
print("REPLACE query executed successfully.")
cursorObj.close()
connection.close()

REPLACE query executed successfully.

In MySQL, the INSERT INTO statement can be used to insert one or more records into a table.

In some scenarios, if a particular column has a UNIQUE constraint and if we are trying to add duplicates records into that particular column using the INSERT INTO statement, MySQL will terminate the statement and returns an error. As the result, no rows are inserted into the table.

MySQL Insert Ignore Statement

However, if we use the MySQL INSERT IGNORE INTO statement, it will not display an error. Instead, it allows us to insert valid data into a table and ignores the rows with invalid data that would cause errors.

Following are some scenarios where the INSERT IGNORE INTO statement avoid errors:

• When we insert a duplicate value in the column of a table that has UNIQUE key or PRIMARY key constraints.
• When we try to add NULL values to a column where it has NOT NULL constraint on it.

Syntax

Following is the syntax of the INSERT IGNORE statement in MySQL −

INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example

First of all, let us create a table named CUSTOMERS using the following query below −

Note: The UNIQUE constraint ensures that no duplicate value can be stored or inserted in the NAME column.

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL UNIQUE,
   PRIMARY KEY (ID)
);

The following query inserts three records into the CUSTOMERS table −

INSERT INTO CUSTOMERS (ID, NAME)
VALUES (1, "Ajay"), (2, "Vinay"), (3, "Arjun");

Execute the following query to display the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following are the records of CUSTOMERS table −

Now, let us insert a duplicate record into the NAME column of CUSTOMERS table using the below query −

INSERT INTO CUSTOMERS (NAME) VALUES (2, "Arjun");

It returns an error because the NAME “Arjun” is already present in the column and hence it violates the UNIQUE constraint.

ERROR 1062 (23000): Duplicate entry ''Arjun'' for key ''customers.NAME''

Now, let us use the INSERT IGNORE statement as shown below −

INSERT IGNORE INTO CUSTOMERS (NAME) VALUES (2, "Arjun");

Output

Though we are inserting a duplicate value, it do not display any error, instead it gives a warning.

Query OK, 0 rows affected, 1 warning (0.00 sec)

We can find the details of the above warning using the following query −

SHOW WARNINGS;

Following is the warnings table −

Verification

If we try to verify the CUSTOMERS table, we can find that the duplicate row which we tried to insert will not be present in the table.

SELECT * FROM CUSTOMERS;

The output for the program above is produced as given below −

MySQL INSERT IGNORE and STRICT mode

The strict mode controls how MySQL handles the invalid, missing, or out of range values that are going to be added into a table through data-change statements such as INSERT or UPDATE.

So, if the strict mode is ON, and we are trying to insert some invalid values into a table using the INSERT statement, MySQL terminates the statement returns an error message.

However, if we use the INSERT IGNORE INTO statement, instead of returning an error, MySQL will adjust those values to make them valid before adding the value to the table.

Example

Let us create a table named CUSTOMERS using the following query −

Note: The NAME column accepts only strings whose length is less than or equal to five.

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(5),
   PRIMARY KEY (ID)
);

Here, we are trying to insert a value into NAME column whose length is greater than 5.

INSERT INTO CUSTOMERS (NAME) VALUES (1, "Malinga");

It returns an error as shown below −

ERROR 1406 (22001): Data too long for column ''NAME'' at row 1

Now, we are trying to use the INSERT IGNORE statement to insert the same string −

INSERT IGNORE INTO CUSTOMERS (NAME) VALUES (1, "Malinga");

Output

As we can see in the output, instead of returning an error, it displays an warning −

Query OK, 1 row affected, 1 warning (0.01 sec)

Let us find the details of the above warning using the following command −

SHOW WARNINGS;

As we can see in the output below, MySQL truncated the data before inserting it into the CUSTOMERS table.

Verification

Execute the following query to verify the records of the CUSTOMERS table −

Select * from CUSTOMERS;

As we can see in the CUSTOMERS table below, the value has been truncated to 5 characters and inserted into the table.

Insert Ignore Query Using a Client Program

Besides using MySQL queries to perform the Insert Ignore operation, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)";
$mysqli->query($sql);

sql = "INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)";
con.query(sql);

String sql = "INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)";
statement.executeUpdate(sql);

insert_ignore_query = "INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)"
cursorObj.execute(insert_ignore_query)

Example

Following are the programs −

$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.
'');
$sql = "INSERT IGNORE INTO tutorials_tbl values(5, ''Java Tutorial'', ''newauther3'', ''2022-11-15'')";
if($result = $mysqli->query($sql)){
   printf("Data inserted successfully..!");
}
$q = "SELECT * FROM tutorials_tbl where tutorial_id = 5";
if($res = $mysqli->query($q)){
printf("Records after insert ignore statement: ");
while($row = mysqli_fetch_row($res)){
   print_r ($row);
}
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Data inserted successfully..!Records after insert ignore statement: Array
(
  [0] => 5
  [1] => Java Tutorial
  [2] => newauther3
  [3] => 2022-11-15
)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating Table
  sql = "CREATE TABLE STUDENTS (ID int AUTO_INCREMENT,NAME varchar(5) NOT NULL UNIQUE,PRIMARY KEY (ID));"
  con.query(sql);

  sql = "INSERT INTO STUDENTS (NAME) VALUES (''Ajay''), (''Vinay''), (''Arjun'');"
  con.query(sql);

  sql= "INSERT IGNORE INTO STUDENTS (NAME) VALUES (''Arjun'');"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
      console.log("----------------------------------------");
  });

  sql = "SHOW WARNINGS;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("----------------------------------------");
  });

  sql = "SELECT * FROM STUDENTS;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Connected!
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '''',
  serverStatus: 2,
  warningStatus: 1,
  changedRows: 0
}
----------------------------------------
[
  {
    Level: ''Warning'',
    Code: 1062,
    Message: "Duplicate entry ''Arjun'' for key ''students.NAME''"
  }
]
----------------------------------------
[
  { ID: 1, NAME: ''Ajay'' },
  { ID: 3, NAME: ''Arjun'' },
  { ID: 2, NAME: ''Vinay'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InsertIgnore {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM STUDENTS";
            rs = st.executeQuery(sql);
            System.out.println("Table records before insert ignore: ");
            while(rs.next()) {
              String Id = rs.getString("Id");
              String Name = rs.getString("Name");
              System.out.println("Id: " + Id + ", Name: " + Name);
            }
            String sql1 = "INSERT INTO STUDENTS (NAME) VALUES ("Arjun")";
            st.executeUpdate(sql1);
            System.out.println("Record with name ''Arjun'' inserted successfully...!");
            String sql2 = "SELECT * FROM STUDENTS";
            rs = st.executeQuery(sql2);
            System.out.println("Table record after insert: ");
            while(rs.next()) {
              String Id = rs.getString("Id");
              String Name = rs.getString("Name");
              System.out.println("Id: " + Id + ", Name: " + Name);
            }
            //now let use insert ignore query to insert a duplicate records into the Students table
            String sql3 = "INSERT IGNORE INTO STUDENTS (NAME) VALUES ("Arjun")";
            st.executeUpdate(sql3);
            System.out.println("Insert ignore query executed successfully....!");
            String sql4 = "SELECT * FROM STUDENTS";
            rs = st.executeQuery(sql4);
            System.out.println("Table records after insert ingore: ");
            while(rs.next()) {
              String Id = rs.getString("Id");
              String Name = rs.getString("Name");
              System.out.println("Id: " + Id + ", Name: " + Name);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records before insert ignore:
Id: 1, Name: Ajay
Id: 2, Name: Vinay
Record with name ''Arjun'' inserted successfully...!
Table record after insert:
Id: 1, Name: Ajay
Id: 9, Name: Arjun
Id: 2, Name: Vinay
Insert ignore query executed successfully....!
Table records after insert ingore:
Id: 1, Name: Ajay
Id: 9, Name: Arjun
Id: 2, Name: Vinay

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
insert_ignore_query = "INSERT IGNORE INTO tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (7, ''New Tutorial'', ''John Doe'', ''2023-07-25'')"
cursorObj.execute(insert_ignore_query)
connection.commit()
print("INSERT IGNORE query executed successfully.")
cursorObj.close()
connection.close()

INSERT IGNORE query executed successfully.

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 −

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.

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.

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 −

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.

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 −

$sql = "INSERT INTO my_table (column1, column2, ...)
VALUES (value1, value2), (value3, value4), ...
ON DUPLICATE KEY UPDATE
 column1 = value1,
 column2 = value2, ...";
$mysqli->query($sql);

sql = "INSERT INTO my_table (column1, column2, ...)
VALUES  (value1, value2), (value3, value4), ...
ON DUPLICATE KEY UPDATE
 column1 = value1,
 column2 = value2, ...";
con.query(sql);

String sql = "INSERT INTO my_table (column1, column2, ...)
VALUES (value1, value2), (value3, value4), ...
ON DUPLICATE KEY UPDATE
 column1 = value1,
 column2 = value2, ...";
statement.executeUpdate(sql);

insert_on_duplicate_key_update_query = "INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1), column2 = VALUES(column2), ..."
cursorObj.execute(insert_on_duplicate_key_update_query)

Example

Following are the programs −

$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();

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
)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("----------------------------------------");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating Table
  sql = "CREATE TABLE Actors (ID int auto_increment,NAME varchar(20) NOT NULL,LATEST_FILM varchar(20),Primary Key (ID));"
  con.query(sql);

  sql= "INSERT INTO Actors (NAME, LATEST_FILM)VALUES (''Prabhas'', ''Salaar''),(''Ram Charan'', ''Game changer''),(''Allu Arjun'', ''Pushpa2'');"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
      console.log("----------------------------------------");
  });

  sql = "SELECT * FROM Actors;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("----------------------------------------");
  });

  sql = "INSERT INTO Actors (ID, NAME) VALUES (3, ''Fahad'') ON DUPLICATE KEY UPDATE NAME = ''Fahad"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("----------------------------------------");
  });

  sql = "SELECT * FROM Actors;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Connected!
----------------------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 1,
  info: ''Records: 3  Duplicates: 0  Warnings: 0'',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
----------------------------------------
[
  { ID: 1, NAME: ''Prabhas'', LATEST_FILM: ''Salaar'' },
  { ID: 2, NAME: ''Ram Charan'', LATEST_FILM: ''Game changer'' },
  { ID: 3, NAME: ''Allu Arjun'', LATEST_FILM: ''Pushpa2'' }
]
----------------------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 2,
  insertId: 3,
  info: '''',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
----------------------------------------
[
  { ID: 1, NAME: ''Prabhas'', LATEST_FILM: ''Salaar'' },
  { ID: 2, NAME: ''Ram Charan'', LATEST_FILM: ''Game changer'' },
  { ID: 3, NAME: ''Fahad'', LATEST_FILM: ''Pushpa2'' }
]

public class InsertOnDuplicate {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM Actors";
            rs = st.executeQuery(sql);
            System.out.println("Table records before insert on duplicate key update: ");
            while(rs.next()) {
              String id = rs.getString("ID");
              String name = rs.getString("NAME");
              String latest_film = rs.getString("LATEST_FILM");
              System.out.println("Id: " + id + ", Name: " + name + ", Latest_film: " + latest_film);
            }
            //let use insert on duplicate update
            String sql1 = "INSERT INTO Actors (ID, NAME) VALUES (3, "Ravi") ON DUPLICATE KEY UPDATE NAME = "Ravi"";
            st.executeUpdate(sql1);
            System.out.println("Query insert on duplicate key update executed successfully....!");
            String sql2 = "SELECT * FROM Actors";
            rs = st.executeQuery(sql2);
            System.out.println("Table records after insert on duplicate update: ");
            while(rs.next()) {
              String id = rs.getString("ID");
              String name = rs.getString("NAME");
              String latest_film = rs.getString("LATEST_FILM");
              System.out.println("Id: " + id + ", Name: " + name + ", Latest_film: " + latest_film);
            }

    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records before insert on duplicate key update:
Id: 1, Name: Prabhas, Latest_film: Salaar
Id: 2, Name: Ram Charan, Latest_film: Game changer
Id: 3, Name: Allu Arjun, Latest_film: Pushpa2
Query insert on duplicate key update executed successfully....!
Table records after insert on duplicate update:
Id: 1, Name: Prabhas, Latest_film: Salaar
Id: 2, Name: Ram Charan, Latest_film: Game changer
Id: 3, Name: Ravi, Latest_film: Pushpa2

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
insert_on_duplicate_key_update = "INSERT INTO tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (7, ''New Tutorial'', ''John Doe'', ''2023-07-25'') ON DUPLICATE KEY UPDATE tutorial_title=''Updated Tutorial'', tutorial_author=''Jane Smith'', submission_date=''2023-07-28''"
cursorObj.execute(insert_on_duplicate_key_update)
connection.commit()
print("INSERT on duplicate key update query executed successfully.")
cursorObj.close()
connection.close()

INSERT on duplicate key update query executed successfully.

MySQL views are a type of virtual tables. They are stored in the database with an associated name. They allow users to do the following −

• Structure data in a way that users or classes of users find natural or intuitive.
• Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
• Summarize data from various tables which can be used to generate reports.

A view can be created from one or more tables, containing either all or selective rows from them. Unless indexed, a view does not exist in a database.

MySQL Create View Statement

Creating a view is simply creating a virtual table using a query. A view is an SQL statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query.

Syntax

Following is the syntax of the CREATE VIEW Statement −

CREATE VIEW view_name AS select_statements FROM table_name;

Example

Assume we have created a table using the SELECT statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 2),
   PRIMARY KEY(ID)
);

Let us insert 7 records in the above created table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2500),
(4, ''Chaitali'', ''26'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''MP'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

Following query creates a view based on the above create table −

CREATE VIEW first_view AS SELECT * FROM CUSTOMERS;

Verification

You can verify the contents of a view using the select query as shown below −

SELECT * FROM first_view;

The view will be created as follows −

With REPLACE and IF NOT EXISTS Clauses

Usually, if you try to create a view with the name same as an existing view an error will be generated as shown as −

CREATE VIEW first_view AS SELECT * FROM CUSTOMERS;

As the view already exists, following error is raised −

ERROR 1050 (42S01): Table ''first_view'' already exists

So, you can use the REPLACE clause along with CREATE VIEW to replace the existing view.

CREATE OR REPLACE VIEW first_view AS SELECT * FROM CUSTOMERS;

With WHERE Clause

We can also create a view using the where clause as shown below −

CREATE VIEW test_view AS SELECT * FROM CUSTOMERS WHERE SALARY>3000;

Following are the contents of the above created view −

The With Check Option

The WITH CHECK OPTION is an option used with CREATE VIEW statement. The purpose of this WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) in the query. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

Syntax

Following is the syntax −

CREATE VIEW view_name
AS SELECT column_name(s)
FROM table_name
WITH CHECK OPTION;

Example

In the following example, we are creating a view using CREATE VIEW statement along with the WITH CHECK OPTION −

CREATE VIEW NEW_VIEW
AS SELECT * FROM CUSTOMERS
WHERE NAME IS NOT NULL
WITH CHECK OPTION;

The view is created as follows −

Creating a MySQL View Using Client Program

In addition to creating a view in MySQL Database using the SQL queries, we can also do so using a client program.

Syntax

Following are the syntaxes of the Create View into MySQL in various programming languages −

$sql="CREATE VIEW views_name AS
   SELECT col_1, col_2, col_3 FROM table_name";
$mysqli->query($sql);

sql="CREATE VIEW view_name AS Select_statements FROM table";
con.query(sql);

String sql="CREATE VIEW views_name AS
   SELECT col_1, col_2, col_3 FROM table_name";
statement.execute(sql);

create_view_query = "CREATE VIEW view_name AS Select_statements FROM table"
cursorObj.execute(create_view_query);

Example

Following are the implementations of this operation in various programming languages −

$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.
'');

// CREATING A VIEW;
$sql = "CREATE VIEW first_view AS
        SELECT tutorial_id, tutorial_title, tutorial_author
        FROM clone_table";
if ($mysqli->query($sql)) {
   printf("View created successfully!.
");
}
if ($mysqli->errno) {
   printf("View could not be created!.
", $mysqli->error);
}

$mysqli->close();

View created successfully!.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

//Connecting to MySQL
con.connect(function (err) {
if (err) throw err;
console.log("Connected!");
console.log("--------------------------");

sql = "USE TUTORIALS"
con.query(sql);

sql = "CREATE TABLE dispatches_data(ProductName VARCHAR(255),CustomerName VARCHAR(255),DispatchTimeStamp timestamp,Price INT,Location VARCHAR(255));"
con.query(sql);

sql = "insert into dispatches_data values(''Key-Board'', ''Raja'', TIMESTAMP(''2019-05-04'', ''15:02:45''), 7000, ''Hyderabad''),(''Earphones'', ''Roja'', TIMESTAMP(''2019-06-26'', ''14:13:12''), 2000, ''Vishakhapatnam''),(''Mouse'', ''Puja'', TIMESTAMP(''2019-12-07'', ''07:50:37''), 3000, ''Vijayawada''),(''Mobile'', ''Vanaja'' , TIMESTAMP (''2018-03-21'', ''16:00:45''), 9000, ''Chennai''),(''Headset'', ''Jalaja'' , TIMESTAMP(''2018-12-30'', ''10:49:27''), 6000, ''Goa'');"
con.query(sql);

sql = "Create view first_view AS SELECT * FROM dispatches_data;"
con.query(sql);

sql = "SELECT * FROM first_view;"
con.query(sql, function(err, result){
  if (err) throw err
  console.log(result);
});
});

Connected!
--------------------------
[
  {
    ProductName: ''Key-Board'',
    CustomerName: ''Raja'',
    DispatchTimeStamp: 2019-05-04T09:32:45.000Z,
    Price: 7000,
    Location: ''Hyderabad''
  },
  {
    ProductName: ''Earphones'',
    CustomerName: ''Roja'',
    DispatchTimeStamp: 2019-06-26T08:43:12.000Z,
    Price: 2000,
    Location: ''Vishakhapatnam''
  },
  {
    ProductName: ''Mouse'',
    CustomerName: ''Puja'',
    DispatchTimeStamp: 2019-12-07T02:20:37.000Z,
    Price: 3000,
    Location: ''Vijayawada''
  },
  {
    ProductName: ''Mobile'',
    CustomerName: ''Vanaja'',
    DispatchTimeStamp: 2018-03-21T10:30:45.000Z,
    Price: 9000,
    Location: ''Chennai''
  },
  {
    ProductName: ''Headset'',
    CustomerName: ''Jalaja'',
    DispatchTimeStamp: 2018-12-30T05:19:27.000Z,
    Price: 6000,
    Location: ''Goa''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CreateView {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Create View.....
         String sql = "CREATE VIEW first_view AS SELECT tutorial_id, tutorial_title, tutorial_author FROM tutorials_tbl";
         statement.execute(sql);
         System.out.println("View created Successfully...!");
         ResultSet resultSet = statement.executeQuery("SELECT * FROM first_view");
         while (resultSet.next()) {
            System.out.print(resultSet.getString(1)+ " " +resultSet.getString(2));
            System.out.println();
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
View created Successfully...!
1 Learn PHP
2 Learn MySQL
3 JAVA Tutorial
4 Python Tutorial
5 Hadoop Tutorial

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
cursorObj = connection.cursor()
create_view_query = """
CREATE VIEW tutorial_view AS
SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl
WHERE submission_date >= ''2023-01-01''
"""
cursorObj.execute(create_view_query)
connection.commit()
print("View created successfully.")
cursorObj.close()
connection.close()

View created successfully.

The MySQL Insert Into Select Statement

In MySQL, the INSERT INTO… SELECT statement is used to add/insert one or more rows from an existing table to target table.

This statement is a combination of two different statements: INSERT INTO and SELECT.

• The MySQL INSERT INTO statement is a commonly used command in database management and it requires only the name of the table and the values to be inserted into a table. However, it is important to ensure that the data being inserted matches the structure and data types of the table columns.
• The SELECT statement is used to fetch data from an existing database table.

When the above mentioned statements are used together, the SELECT statement first fetches the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).

Syntax

Following is the syntax for using insert into select statement −

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

Following are some important points that we have to consider before we execute the below queries −

• In the database where we are going to insert data, a table must already exist.
• Both the source and target tables must match its structure.

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)
);

The following query inserts 7 records into the above created table −

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 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to retrieve all the records from CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

Inserting Required Data from one Table to Another Table

We may come across some instances where we only want to add small number of records to another table. This can be achieved by using a WHERE clause to select all the number of rows that the query returned.

Example

Before that, let us create a another table named CUSTOMERS_copy with similar structure of previously created CUSTOMERS table −

CREATE TABLE CUSTOMERS_copy (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

In the following query, we are trying to fetch the records from the CUSTOMERS table and insert them into the CUSTOMERS_copy table.

INSERT INTO CUSTOMERS_copy (ID, NAME, AGE, ADDRESS, SALARY)
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
WHERE AGE >= 25;

Output

The output for the program above is produced as given below −

Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Verification

To confirm if the records from the ”CUSTOMERS” table, where the age is 25 or older, have been inserted to the target table ”CUSTOMERS_copy”, execute the following query −

SELECT * FROM CUSTOMERS_copy;

Following are the records whose age is 25 or older −

Inserting the rows with LIMIT

Using the MySQL LIMIT clause, we can specify the number of rows from the query that should be added to the target table.

Example

Before proceeding further, let us first truncate all rows in the CUSTOMERS_copy table using the following query −

TRUNCATE TABLE CUSTOMERS_copy;

Now, we are going to insert the top 3 records from CUSTOMERS table sorted by their AGE using the LIMIT clause −

INSERT INTO CUSTOMERS_copy (ID, NAME, AGE, ADDRESS, SALARY)
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
ORDER BY AGE LIMIT 3;

Output

The output for the program above is produced as given below −

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Verification

Execute the following query to verify whether the records are reflected in the CUSTOMERS_copy table or not −

SELECT * FROM CUSTOMERS_copy;

Following are the records −

Inserting All Columns from one Table to Another Table

We can also insert every column from one to another table. To do so, following is the syntax −

INSERT INTO table2
SELECT * FROM table1

Before inserting all the records, first truncate all rows in the CUSTOMERS_copy table by using the statement −

TRUNCATE TABLE CUSTOMERS_copy;

In the following query, we are trying to add all the columns from the CUSTOMERS table to the CUSTOMERS_copy table −

INSERT INTO CUSTOMERS_copy SELECT * FROM CUSTOMERS;

Output

All the columns have been inserted without any errors.

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

We can verify whether the changes are reflected in a CUSTOMERS_copy table by retrieving its contents using the SELECT statement.

SELECT * FROM CUSTOMERS_copy;

Following is the CUSTOMERS_copy table −

INSERT INTO SELECT Using a Client Program

Besides using MySQL queries to perform the INSERT INTO … SELECT statement, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "INSERT INTO new_tutorials_tbl SELECT * FROM tutorials_tbl WHERE tutorial_id = 2";
$mysqli->query($sql);

sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = ''Male''";
con.query(sql);

String sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = "Male"";
st.executeUpdate(sql);

sql = "INSERT INTO new_tutorials_tbl
(tutorial_id, tutorial_title, tutorial_author, submission_date)
SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"
insert_into_select_query = sql
cursorObj.execute(insert_into_select_query)

Example

Following are the programs −

$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 new_tutorials_tbl";
if($res = $mysqli->query($q)){
   printf("Select query executed successfully..!n");
   printf("The table ''new_tutorials_tbl'' records before insert into select query: n");
   while($r = mysqli_fetch_array($res)){
       print_r ($r);
   }
}
$sql = "INSERT INTO new_tutorials_tbl SELECT * FROM tutorials_tbl WHERE tutorial_id = 2";
if($result = $mysqli->query($sql)){
   printf("Insert into select query executed successfully..! n");
}
$q = "SELECT * FROM new_tutorials_tbl";
if($res = $mysqli->query($q)){
   printf("Select query executed successfully..!n");
   printf("The table ''new_tutorials_tbl'' records after insert into select query: n");
   while($r = mysqli_fetch_array($res)){
       print_r ($r);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Select query executed successfully..!
The table ''new_tutorials_tbl'' records before insert into select query:
Array
(
    [0] => 1
    [tutorial_id] => 1
    [1] => Java Tutorial
    [tutorial_title] => Java Tutorial
    [2] => new_author
    [tutorial_author] => new_author
    [3] =>
    [submission_date] =>
)
Insert into select query executed successfully..!
Select query executed successfully..!
The table ''new_tutorials_tbl'' records after insert into select query:
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
)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("----------------------------------------");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE Agent (ID int NOT NULL,NAME varchar(20) NOT NULL,GENDER varchar(20) NOT NULL,AGE int NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  sql = "INSERT INTO Agent VALUES (1,''Msd'', ''Male'', 21), (2,''Virat'', ''Male'', 23), (3,''Perry'', ''Female'', 24), (4,''Smiti'', ''Female'', 18), (5,''Rose'', ''Female'', 23);"
  con.query(sql);

  sql = "SELECT * FROM Agent;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("-------------------------------------------------------");
  });

  sql = "CREATE TABLE Agentdemo(ID int NOT NULL,NAME varchar(50) NOT NULL,GENDER varchar(20) NOT NULL,AGE int NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting required data from one table to another
  sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = ''Male''";
  con.query(sql);

  sql = "SELECT * FROM Agentdemo;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Connected!
----------------------------------------
[
  { ID: 1, NAME: ''Msd'', GENDER: ''Male'', AGE: 21 },
  { ID: 2, NAME: ''Virat'', GENDER: ''Male'', AGE: 23 },
  { ID: 3, NAME: ''Perry'', GENDER: ''Female'', AGE: 24 },
  { ID: 4, NAME: ''Smiti'', GENDER: ''Female'', AGE: 18 },
  { ID: 5, NAME: ''Rose'', GENDER: ''Female'', AGE: 23 }
]
-------------------------------------------------------
[
  { ID: 1, NAME: ''Msd'', GENDER: ''Male'', AGE: 21 },
  { ID: 2, NAME: ''Virat'', GENDER: ''Male'', AGE: 23 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InsertIntoSelect {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM Agent";
            rs = st.executeQuery(sql);
            System.out.println("Agent table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
            String sql1 = "SELECT * FROM Agentdemo";
            rs = st.executeQuery(sql1);
            System.out.println("Agentdemo table before after insert into select: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
            //now let use the insert into select query
            String sql2 = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = "Male"";
            st.executeUpdate(sql2);
            String sql3 = "SELECT * FROM Agentdemo";
            rs = st.executeQuery(sql3);
            System.out.println("Agentdemo table after after insert into select: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Agent table records:
Id: 1, Name: MsdGender: Male, Age: 21
Id: 2, Name: ViratGender: Male, Age: 23
Id: 3, Name: PerryGender: Female, Age: 24
Id: 4, Name: SmitiGender: Female, Age: 18
Id: 5, Name: RoseGender: Female, Age: 23
Agentdemo table before after insert into select:
Agentdemo table after after insert into select:
Id: 1, Name: MsdGender: Male, Age: 21
Id: 2, Name: ViratGender: Male, Age: 23

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
insert_into_select_query = "INSERT INTO new_tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"
cursorObj.execute(insert_into_select_query)
connection.commit()
print("Data inserted into new table successfully.")
cursorObj.close()
connection.close()

Data inserted into new table successfully.