Database Management System (DBMS) / SQL(STRUCTURED QUERY LANGUAGE)

REPLACE vs INSERT

📌 Base Table (used for all examples)

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(2),
    section CHAR(1)
);

INSERT INTO students (id, name, age, grade, section) VALUES
(1, 'Anya', 17, 'A', 'A'),
(2, 'Raj', 18, 'B', 'B');

We will use this same table for all commands below.

➕ INSERT — Add New Rows Only

The INSERT command adds new rows.

❗ If the primary key already exists → it throws an error.

Example

INSERT INTO students (id, name, age, grade, section)
VALUES (3, 'Swastika', 17, 'B', 'C');

✔️ Adds a new third row
❌ Fails if id = 3 already exists

🔁 REPLACE — Insert or Replace Row

REPLACE will:

➡️ insert if the primary key is new
➡️ delete + insert if primary key already exists

Example

REPLACE INTO students (id, name, age, grade, section)
VALUES (1, 'Swasti', 17, 'A', 'C');

✔️ If id = 1 exists → old row removed
✔️ new row inserted

⚠️ ALL old row values get wiped

⚠️ Important Notes

  • REPLACE is NOT like UPDATE

  • It is delete + new insert

  • Missing columns = old values lost

  • Auto-increment values can skip

✅ Summary Table

Feature

INSERT

REPLACE

Adds new row

✅

✅

Fails if key exists

✅

❌

Deletes old row

❌

✅

Updates values safely

❌

❌

Risk level

Low

High

🧠 Which to Use?

✔️ Use INSERT when adding new records
✔️ Use REPLACE only when overwriting is intended
✔️ For safe overwrite → use this (MySQL):

INSERT INTO students (id, name, age, grade, section)
VALUES (1, 'Anya', 17, 'A', 'A')
ON DUPLICATE KEY UPDATE
name='Anya', age=17, grade='A', section='A';

✔️ Updates only given fields
✔️ Doesn’t delete entire row