MySQL - REPLACE Query

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

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.