Replace Query in MySQL

Introduction

In MySQL, sometimes you want to insert a new record, but if a record with the same primary key or unique key already exists, you want to replace it.

This is done using the REPLACE query.

What is the REPLACE Query

The REPLACE INTO statement is used to:

  • Insert a new row if no duplicate exists
  • Delete the existing row and insert a new one if a duplicate key exists

It works based on:

  • PRIMARY KEY
  • UNIQUE KEY

Basic Syntax

REPLACE INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

Example

REPLACE INTO Students (id, name, age)

VALUES (1, 'Rahul', 22);

If a record with id = 1 exists:

  • It will be deleted
  • A new record will be inserted

How REPLACE Works Internally

  1. MySQL checks for duplicate key (PRIMARY or UNIQUE)
  2. If found → deletes the existing row
  3. Inserts the new row

Important Behavior

  • It deletes and inserts, not updates
  • AUTO_INCREMENT may change
  • Triggers DELETE and INSERT events

Example Scenario

Initial data:

id

name

age

1

Rahul

20

Query:

REPLACE INTO Students (id, name, age)

VALUES (1, 'Rahul Sharma', 22);

Result:

id

name

age

1

Rahul Sharma

22

REPLACE vs INSERT

Feature

REPLACE

INSERT

Duplicate key

Replaces row

Error

Operation

DELETE + INSERT

INSERT only

Data overwrite

Yes

No

REPLACE vs UPDATE

Feature

REPLACE

UPDATE

Operation

Delete + Insert

Modify existing row

Efficiency

Less efficient

More efficient

Use case

Replace full row

Modify specific fields

When to Use REPLACE

  • When you want to overwrite entire rows
  • When handling duplicate keys automatically
  • When inserting or replacing data in one step

Common Mistakes

  • Assuming it updates data (it actually deletes + inserts)
  • Losing data not included in the query
  • Ignoring AUTO_INCREMENT changes
  • Using without a PRIMARY or UNIQUE key

Key Points to Remember

  • REPLACE inserts or replaces rows
  • Works on PRIMARY or UNIQUE keys
  • Deletes existing row before inserting new one
  • Not the same as UPDATE
  • Use carefully to avoid data loss