ON DUPLICATE KEY UPDATE in MySQL - Mini Project: Build & Apply

Using ON DUPLICATE KEY UPDATE in MySQL

📖 Scenario: You are managing a small online store's product inventory. You want to add new products to the database, but if a product with the same product_id already exists, you want to update its price and stock instead of adding a duplicate entry.

🎯 Goal: Build a MySQL query that inserts a new product into the products table. If the product_id already exists, update the price and stock fields for that product.

📋 What You'll Learn

Create a products table with columns product_id (primary key), name, price, and stock.

Insert a product with specific values for product_id, name, price, and stock.

Add a query that uses ON DUPLICATE KEY UPDATE to update price and stock if the product_id already exists.

Ensure the final query correctly handles both insert and update scenarios.

💡 Why This Matters

🌍 Real World

Managing product inventory in an online store where products may be added or updated frequently.

💼 Career

Database administrators and backend developers often use ON DUPLICATE KEY UPDATE to efficiently handle insert-or-update operations.

1

Create the products table

Create a table called products with these columns: product_id as an integer primary key, name as a VARCHAR(50), price as DECIMAL(10,2), and stock as an integer.

MySQL

-- Create the products table with the specified columns
-- Your code here

Need a hint?

Use CREATE TABLE and define product_id as the primary key.

2

Insert a product

Write an INSERT INTO products statement to add a product with product_id 101, name 'Coffee Mug', price 7.99, and stock 100.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  stock INT
);

-- Insert the product with the specified values
-- Your code here

Need a hint?

Use INSERT INTO products (columns) VALUES (values) with the exact values given.

3

Write the ON DUPLICATE KEY UPDATE query

Write an INSERT INTO products statement to add a product with product_id 101, name 'Coffee Mug', price 8.99, and stock 150. Use ON DUPLICATE KEY UPDATE to update the price and stock columns if the product_id already exists.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  stock INT
);

INSERT INTO products (product_id, name, price, stock) VALUES (101, 'Coffee Mug', 7.99, 100);

-- Insert or update the product with ON DUPLICATE KEY UPDATE
-- Your code here

Need a hint?

Use ON DUPLICATE KEY UPDATE followed by price = VALUES(price) and stock = VALUES(stock).

4

Complete the query with correct syntax

Ensure the final query includes the INSERT INTO products statement with ON DUPLICATE KEY UPDATE that updates price and stock for product_id 101. The query should be syntactically correct and ready to run.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  stock INT
);

INSERT INTO products (product_id, name, price, stock) VALUES (101, 'Coffee Mug', 7.99, 100);

INSERT INTO products (product_id, name, price, stock) VALUES (101, 'Coffee Mug', 8.99, 150)
ON DUPLICATE KEY UPDATE price = VALUES(price), stock = VALUES(stock);

-- Finalize the query syntax if needed
-- Your code here

Need a hint?

Check that the query ends with a semicolon and uses the correct syntax for ON DUPLICATE KEY UPDATE.