Modifying Data with INSERT, UPDATE, DELETE

So far you've practiced reading from the marketplace database. Now it's time to change the data. We'll add new customers and products, update order statuses, and remove outdated rows while protecting the integrity of our relationships.

Learning Objectives

• Insert rows into category, customer, product, and orders
• Update existing rows safely with targeted WHERE clauses
• Delete records while respecting foreign-key constraints
• Apply best practices like transactions and pre-check queries

Inserting Data

INSERT adds brand-new rows. Always list the columns explicitly so your statements survive schema tweaks.

Add a new category and customer

INSERT INTO category (category_id, category_name)
VALUES (7, 'Pet Supplies');

INSERT INTO customer (customer_id, first_name, last_name, email, preferred_category_id, signup_year, city)
VALUES (13, 'Leena', 'Roy', 'leena.roy@shop.local', 7, 2025, 'Surat');

Multiple rows can be inserted in a single statement:

Insert several new products

INSERT INTO product (product_id, sku, product_name, price, stock_qty, category_id, seller_id) VALUES
  (12, 'PT-7001', 'Pet Travel Carrier', 49.00, 40, 7, NULL),
  (13, 'PT-7002', 'Adjustable Harness', 19.00, 120, 7, NULL);

If your database auto-generates IDs (for example, AUTO_INCREMENT in MySQL), omit those columns and let the engine supply the values.

Insert from a SELECT

Populate a table by pulling data from an existing one.

Copy electronics customers into a marketing list

CREATE TABLE electronics_marketing (
  customer_id INT PRIMARY KEY,
  first_name  VARCHAR(80),
  last_name   VARCHAR(80)
);

INSERT INTO electronics_marketing (customer_id, first_name, last_name)
SELECT customer_id, first_name, last_name
FROM customer
WHERE preferred_category_id = 1;

Updating Data

UPDATE changes existing rows. The WHERE clause is mandatory unless you truly intend to alter every row.

Assign a seller to a product

UPDATE product
SET seller_id = 7
WHERE sku = 'PT-7001';

Mark an order as delivered

UPDATE orders
SET status = 'Delivered',
    delivered_at = '2025-04-02'
WHERE order_id = 8
  AND status = 'Paid';

To review affected rows before committing, run the same filter with SELECT:

SELECT *
FROM orders
WHERE order_id = 8
  AND status = 'Paid';

Deleting Data

DELETE removes rows. Use it with precision; foreign-key constraints may block deletes when dependent rows exist.

Remove an order item that was entered in error

DELETE FROM order_item
WHERE order_item_id = 5;

If you need to purge records for a cancelled order:

Clear items for cancelled orders

DELETE FROM order_item
WHERE order_id = 6;

To remove an order entirely, delete dependent order items first or define cascading rules:

START TRANSACTION;
  DELETE FROM order_item WHERE order_id = 12;
  DELETE FROM orders     WHERE order_id = 12;
COMMIT;

Best Practices

• Always test with SELECT: Preview the rows that match your WHERE clause.
• Use transactions: Wrap related INSERT, UPDATE, or DELETE statements in a transaction so you can roll back if needed.
• Respect constraints: Inserting or updating with invalid foreign keys (e.g., a non-existent category_id) will fail.
• Default values: Rely on default settings (like stock_qty defaulting to 0) when appropriate instead of hard-coding them everywhere.
• Logging: In production systems, audit critical changes so you can trace who altered what.

Summary

• INSERT adds data; specify columns for clarity and resilience.
• UPDATE changes data; a narrow WHERE clause keeps modifications safe.
• DELETE removes data; understand dependencies before you run it.
• Transactions and preflight SELECT queries protect you from mistakes.

Quiz

Show quiz

1. Which clause ensures an UPDATE affects only intended rows?
   A) SET
   B) FROM
   C) WHERE
   D) VALUES

2. How can you add two new products in a single statement?
   A) Run two separate INSERT commands
   B) Use INSERT ... VALUES (...), (...);
   C) Use UPDATE with multiple values
   D) Modify the table default

3. True or False: You can delete an order that still has order items without additional steps.
   A) True
   B) False

4. Why should you wrap related modifications in a transaction?
   A) It speeds up queries
   B) It allows rolling back if any statement fails
   C) It automatically creates indexes
   D) It bypasses foreign-key checks

---

Answers

1. C
2. B
3. B
4. B