REPLACE INTO behavior in MySQL

Introduction

REPLACE INTO lets you add a new row or update an existing row in a table using the same command. It helps keep data fresh without writing separate insert or update commands.

When you want to add a new record but update it if it already exists.

When you have a unique key and want to avoid duplicate entries.

When syncing data from another source and want to overwrite old data.

When you want a simple way to refresh a row without checking if it exists first.

Syntax

MySQL

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

REPLACE INTO works like INSERT, but deletes existing rows with the same unique key before inserting.

If no existing row matches, it just inserts a new row.

Examples

Adds a new user with id 1 and name Alice, or replaces the existing user with id 1.

MySQL

REPLACE INTO users (id, name) VALUES (1, 'Alice');

Inserts or replaces a product with SKU 'A123' and price 9.99.

MySQL

REPLACE INTO products (sku, price) VALUES ('A123', 9.99);

Sample Program

First, we create a table and insert one product. Then we use REPLACE INTO to update the quantity of product 101 and add a new product 102. Finally, we select all rows to see the result.

MySQL

CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50),
  quantity INT
);

INSERT INTO inventory VALUES (101, 'Pen', 10);

REPLACE INTO inventory (product_id, product_name, quantity) VALUES (101, 'Pen', 20);
REPLACE INTO inventory (product_id, product_name, quantity) VALUES (102, 'Notebook', 15);

SELECT * FROM inventory ORDER BY product_id;

Important Notes

REPLACE INTO deletes the old row before inserting the new one, so triggers and auto-increment counters may behave differently than UPDATE.

Use REPLACE INTO only when you want to fully overwrite existing rows, not partially update them.

If your table has foreign keys, deleting rows with REPLACE INTO might cause constraint errors.

Summary

REPLACE INTO inserts a new row or replaces an existing row with the same unique key.

It simplifies adding or updating data in one step.

Be careful with side effects because it deletes and reinserts rows.