MySQL - REPLACE Query

Table of content

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 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00

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 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Kaushik 20 Kota 2000.00

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.

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Kaushik 20 Kota 2000.00
3 Chaitali 25 Mumbai 6500.00
4 Hardik 27 Bhopal 8500.00
5 Komal 22 Hyderabad 4500.00

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 −

PHP NodeJS Java Python

To replace a record in a table from MySQL Database through a PHP program we need to execute the Alter statement using the mysqli function query() as −

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

To replace a record in a table from MySQL Database through a Node.js program we need to execute the Alter statement using the query() function of the mysql2 library as −

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

To replace a record in a table from MySQL Database through a Java program we need to execute the Alter statement using the JDBC function executeUpdate() as −

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

To replace a record in a table from MySQL Database through a Java program we need to execute the Alter statement using the execute() function of the MySQL Connector/Python as −

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

Example

Following are the programs −

PHP NodeJS Java Python 
$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();

Output

The output obtained is as follows −

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

Output

The output produced is as follows −

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

Output

The output obtained is as shown below −

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

Output

Following is the output of the above code −

REPLACE query executed successfully.
Advertisements