MySQL REPLACE INTO Statement

The MySQL REPLACE INTO statement is a data manipulation statement that inserts a new row into a table or replaces an existing row when a duplicate value exists in a PRIMARY KEY or UNIQUE KEY column.

If MySQL finds an existing row with the same PRIMARY KEY or UNIQUE KEY value, it first deletes the existing row and then inserts the new row. If no matching row exists, MySQL simply inserts the new row.

Unlike the UPDATE statement, REPLACE INTO does not modify the existing row. Instead, it deletes the existing row completely and inserts a new row with the specified values.

Why Use the MySQL REPLACE INTO Statement?

Suppose you have a table that stores student records. You want to insert a new student’s information. However, if a student with the same ID (PRIMARY KEY or UNIQUE KEY) already exists, you want to replace the existing record with the new one.

Without using the REPLACE INTO statement, you would typically need to write multiple SQL statements, such as:

• Check whether the record already exists.
• Delete the existing record if it exists.
• Insert the new record.

With the REPLACE INTO statement, you can perform this task using a single SQL statement. If no matching record exists, MySQL inserts the new row. If a row with the same PRIMARY KEY or UNIQUE KEY already exists, MySQL automatically deletes the existing row and then inserts the new one.

Therefore, the REPLACE INTO statement is useful when you want to overwrite an existing record without writing separate SELECT, DELETE, and INSERT statements in your application code. It simplifies the operation by automatically handling duplicate key conflicts.

Features of the MySQL REPLACE INTO Statement

The REPLACE INTO statement in MySQL provides several useful features:

• Inserts a new row into a table if no matching PRIMARY KEY or UNIQUE KEY exists.
• Automatically deletes the existing row and inserts a new row when a duplicate PRIMARY KEY or UNIQUE KEY is found.
• Works only with tables that contain a PRIMARY KEY or UNIQUE KEY.
• Executes the operation using a single SQL statement.
• Reduces the amount of application code required.
• Supports inserting multiple rows in a single statement.
• Supports inserting data from another table using the SELECT statement.

Syntax of REPLACE INTO Statement in MySQL

The basic syntax of the REPLACE INTO statement in MySQL is:

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

In this syntax:

• REPLACE INTO specifies that MySQL should insert a new row. If a row with the same PRIMARY KEY or UNIQUE KEY already exists, MySQL deletes the existing row and inserts the new one.
• table_name specifies the name of the table into which the new row will be inserted.
• column1, column2, column3, … specify the columns that will receive the values.
• The VALUES clause specifies the list of values to be inserted into the specified columns.
• value1, value2, value3, … represent the actual data values corresponding to the specified columns.

Create a Sample Table in MySQL

Suppose we have a table named Student in a MySQL database.

CREATE TABLE Student
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Course VARCHAR(40),
    Marks INT
);

This table contains four columns:

• StudentID stores the unique ID of each student.
• Name stores the student’s name.
• Course stores the name of the course in which the student is enrolled.
• Marks stores the student’s marks.

Since StudentID is defined as the PRIMARY KEY, MySQL can identify duplicate rows based on the StudentID value. This enables the REPLACE INTO statement to determine whether to insert a new row or delete the existing row and insert a new one.

Examples of REPLACE INTO Statement

Example 1: Insert a New Row Using REPLACE INTO

We will insert a new row using the following REPLACE INTO statement.

REPLACE INTO Student(StudentID, Name, Course, Marks)
VALUES(101, 'Rahul', 'Java', 85);

Output:

In this example:

• MySQL attempts to insert a row with StudentID = 101 into the Student table.
• Since no existing row has the same PRIMARY KEY value (StudentID = 101), MySQL inserts the new row into the table.
• In this case, the REPLACE INTO statement behaves like a normal INSERT statement because no duplicate key is found.

Example 2: Replace an Existing Row

Suppose the table already contains the following record.

Now execute the following statement.

REPLACE INTO Student(StudentID, Name, Course, Marks)
VALUES(101, 'Rahul', 'Python', 92);

Output:

Let’s see what MySQL does internally:

• MySQL attempts to insert a row with StudentID = 101 in the table.
• It detects that a row with the same PRIMARY KEY (StudentID = 101) already exists in the table.
• MySQL deletes the existing row from the table.
• MySQL inserts a new row with the updated values in the table.
• The value Java has been replaced with Python.
• The value 85 is replaced with 92.

Notice: MySQL does not update the existing row in the table. Instead, it deletes the old row completely and inserts a new row with the specified values.

Example 3: Replace Using Partial Columns

REPLACE INTO Student(StudentID, Name) 
VALUES(102, 'Amit');

Output:

In this example:

• We have assigned values only to the StudentID and Name columns.
• The Course and Marks columns are not specified in the REPLACE statement.
• If an omitted column has a DEFAULT value, MySQL stores the default value.
• Otherwise, if the column allows NULL, MySQL stores NULL.

Example 4: MySQL REPLACE INTO Without Column Names

REPLACE INTO Student 
VALUES(103, 'Priya', 'MySQL', 88);

Output:

In this example:

• We have not specified column names in the MySQL REPLACE INTO statement.
• Since no column names are specified in the REPLACE INTO statement, the values must be provided in the same order as the columns are defined in the table.
• The number of values must match the number of columns in the table.
• This syntax can be used only when you provide a value for every column in the table.

Common Errors While Using REPLACE INTO Statement

1. No PRIMARY KEY or UNIQUE KEY

The REPLACE INTO statement relies on a PRIMARY KEY or UNIQUE KEY to detect duplicate rows. If the table has no PRIMARY KEY or UNIQUE KEY, MySQL cannot find an existing row to replace. Therefore, it always inserts a new row instead of replacing an existing one. For example:

REPLACE INTO Student(Name, Course)
VALUES ('John', 'PHP');

2. Missing Required Column

If you define any omitted column as NOT NULL and it has no default value, MySQL generates an error. However, if you define the omitted columns with DEFAULT values, MySQL inserts those default values. If the omitted columns allow NULL and have no default value, MySQL stores NULL. For example:

REPLACE INTO Student(StudentID)
VALUES (201);

3. Incorrect Number of Values

If you do not specify the column names in the MySQL REPLACE INTO statement, the number of values must exactly match the number of columns in the table. Otherwise, MySQL generates an error such as the column count doesn’t match value count at row 1. For example:

REPLACE INTO Student
VALUES (101, 'Rahul');