REPLACE INTO behavior in MySQL - Mini Project: Build & Apply

Understanding REPLACE INTO Behavior in MySQL

📖 Scenario: You are managing a small customer database for a local store. Sometimes, customers update their contact information, and you want to keep the database up to date without duplicates.

🎯 Goal: Build a simple table and use REPLACE INTO to insert or update customer records based on their unique ID.

📋 What You'll Learn

Create a table called customers with columns id (primary key), name, and email

Insert initial customer data using INSERT INTO

Create a REPLACE INTO statement to update or insert a customer record

Verify the behavior of REPLACE INTO by checking the table contents

💡 Why This Matters

🌍 Real World

Keeping customer records up to date without duplicates is common in retail and service businesses.

💼 Career

Understanding REPLACE INTO helps database administrators and developers manage data efficiently when updates or inserts are needed based on unique keys.

1

Create the customers table

Write a SQL statement to create a table called customers with three columns: id as an integer primary key, name as a VARCHAR(50), and email as a VARCHAR(100).

MySQL

-- Write the CREATE TABLE statement for customers
-- Your code here

Need a hint?

Use CREATE TABLE customers and define id as INT PRIMARY KEY.

2

Insert initial customer data

Write a SQL statement to insert two customers into the customers table: one with id 1, name 'Alice', and email '[e-mail]'; another with id 2, name 'Bob', and email '[e-mail]'.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);
-- Insert two customers with ids 1 and 2
-- Your code here

Need a hint?

Use INSERT INTO customers (id, name, email) VALUES with two sets of values.

3

Use REPLACE INTO to update or insert a customer

Write a SQL statement using REPLACE INTO customers to update the customer with id 2 to have name 'Robert' and email '[e-mail]'.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice', '[e-mail]'),
(2, 'Bob', '[e-mail]');
-- Use REPLACE INTO to update customer with id 2
-- Your code here

Need a hint?

Use REPLACE INTO customers (id, name, email) VALUES (2, 'Robert', '[e-mail]').

4

Add a new customer using REPLACE INTO

Write a SQL statement using REPLACE INTO customers to add a new customer with id 3, name 'Charlie', and email '[e-mail]'.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice', '[e-mail]'),
(2, 'Bob', '[e-mail]');

REPLACE INTO customers (id, name, email) VALUES
(2, 'Robert', '[e-mail]');
-- Use REPLACE INTO to add a new customer with id 3
-- Your code here

Need a hint?

Use REPLACE INTO customers (id, name, email) VALUES (3, 'Charlie', '[e-mail]').