MySQL - Replace Query

MySQL - The REPLACE Statement

The MySQL REPLACE statement is used to insert rows into a table. Its behavior depends on whether the new row conflicts with an existing row on a PRIMARY KEY or UNIQUE index.

How REPLACE Works

• If no key conflict exists: REPLACE acts exactly like an INSERT statement, adding a new row to the table.
• If a key conflict exists: REPLACE first deletes the existing row that has the conflicting key value, and then inserts the new row. It is functionally equivalent to a DELETE followed by an INSERT.

Syntax

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- or using the SET syntax:
REPLACE INTO table_name
SET column1 = value1, column2 = value2, ...;

Crucial Comparison: REPLACE vs. INSERT ... ON DUPLICATE KEY UPDATE

Before using REPLACE, it’s critical to understand its alternative, INSERT ... ON DUPLICATE KEY UPDATE (often called an “upsert”). They are not the same, and the difference is significant.

Feature	REPLACE	INSERT … ON DUPLICATE KEY UPDATE
Operation	DELETE + INSERT	INSERT or UPDATE
Triggers	Fires DELETE triggers, then INSERT triggers.	Fires INSERT triggers (on new row) or UPDATE triggers (on existing row).
Auto-increment ID	If the table has an auto-increment PK, a new ID is generated because the old row is deleted.	The existing auto-increment ID is preserved because the row is updated in place.
Column Values	All columns in the old row are removed. Columns not specified in the REPLACE statement are set to their default values.	Only the columns specified in the UPDATE clause are changed. Other columns retain their existing values.

Best Practice: In most scenarios where you want to “insert or update” a row, INSERT ... ON DUPLICATE KEY UPDATE is the safer and more intuitive choice. Use REPLACE only when you specifically intend to completely remove the old row and create a new one, including all associated side effects like firing delete triggers or generating a new auto-increment ID.

Examples

Let’s set up a products table with a UNIQUE constraint on the sku.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2)
);

INSERT INTO products (sku, name, price) VALUES
('A-001', 'Gaming Mouse', 49.99),
('B-002', 'Mechanical Keyboard', 129.50);

Example 1: Using REPLACE as an INSERT

Since the SKU ‘C-003’ does not exist, this statement acts as a simple insert.

REPLACE INTO products (sku, name, price)
VALUES ('C-003', 'USB-C Hub', 35.00);

Example 2: Using REPLACE to Replace an Existing Row

The SKU ‘A-001’ already exists. REPLACE will delete the original ‘Gaming Mouse’ row and insert this new one. If you check the id column, you’ll see it has been assigned a new auto-increment value.

-- The old row with id=1 and sku='A-001' is deleted.
-- A new row is inserted.
REPLACE INTO products (sku, name, price)
VALUES ('A-001', 'Wireless Gaming Mouse', 69.99);

Example 3: The Preferred Alternative (INSERT ... ON DUPLICATE KEY UPDATE)

This query attempts to insert a new row for SKU ‘B-002’. Because it already exists, it will instead perform an UPDATE, changing only the price. The id will remain unchanged.

INSERT INTO products (sku, name, price)
VALUES ('B-002', 'Mechanical Keyboard', 119.99)
ON DUPLICATE KEY UPDATE
    price = VALUES(price); -- Updates the price using the value from the INSERT part.

Using REPLACE in a Client Program

Here are examples of using both REPLACE and the preferred INSERT ... ON DUPLICATE KEY UPDATE from application code.

Python
NodeJS
Java
PHP



import mysql.connector

config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

# Using REPLACE
replace_query = "REPLACE INTO products (sku, name, price) VALUES (%s, %s, %s)"
replace_data = ('D-004', 'Webcam', 59.99)

# Using INSERT ... ON DUPLICATE KEY UPDATE (Preferred)
upsert_query = """
    INSERT INTO products (sku, name, price) VALUES (%s, %s, %s)
    ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)
"""
upsert_data = ('D-004', 'HD Webcam', 65.00)

try:
    with mysql.connector.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute(replace_query, replace_data)
            print(f"{cursor.rowcount} row(s) affected by REPLACE.")

            cursor.execute(upsert_query, upsert_data)
            # An UPDATE returns 2 affected rows if the row was updated
            print(f"{cursor.rowcount} row(s) affected by INSERT...ON DUPLICATE KEY UPDATE.")
except mysql.connector.Error as err:
    print(f"Error: {err}")



const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: '127.0.0.1',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database'
});

const manageProduct = async () => {
    const upsertQuery = `
        INSERT INTO products (sku, name, price)
        VALUES (?, ?, ?)
        ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)
    `;
    const productData = ['E-005', 'Monitor', 250.00];

    try {
        // First, insert the product
        let [result] = await pool.execute(upsertQuery, productData);
        console.log(`Initial insert/update affected ${result.affectedRows} row(s).`);

        // Now, update the same product with a new price
        const updatedProductData = ['E-005', '4K Monitor', 399.99];
        [result] = await pool.execute(upsertQuery, updatedProductData);
        console.log(`Second insert/update affected ${result.affectedRows} row(s).`);
    } catch (error) {
        console.error('Query failed:', error);
    } finally {
        await pool.end();
    }
};

manageProduct();



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.math.BigDecimal;

public class ReplaceQuery {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_user";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String upsertQuery = """
            INSERT INTO products (sku, name, price)
            VALUES (?, ?, ?)
            ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)
        """;

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(upsertQuery)) {

            // Set values for the prepared statement
            pstmt.setString(1, "F-006");
            pstmt.setString(2, "Laptop Stand");
            pstmt.setBigDecimal(3, new BigDecimal("25.50"));

            int affectedRows = pstmt.executeUpdate();
            System.out.printf("Query affected %d row(s).%n", affectedRows);

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



<?php
$host = '127.0.0.1';
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

// Preferred "upsert" method
$query = "
    INSERT INTO products (sku, name, price)
    VALUES (:sku, :name, :price)
    ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)
";

$productData = [
    'sku' => 'G-007',
    'name' => 'Ergonomic Chair',
    'price' => 299.00
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    $stmt = $pdo->prepare($query);
    $stmt->execute($productData);

    echo "Query executed. {$stmt->rowCount()} row(s) were affected.\n";

} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

?>