Exercise 7: Data Modification (DML) ✏️

🎯 Objective

Master INSERT, UPDATE, DELETE operations and transaction management for safe data manipulation.

📋 Problem Statement

Data Modification Language (DML) operations change data in tables. Understanding transactions ensures data integrity.

📥 Tasks

Task 1: Basic INSERT

Insert a new product: "Wireless Keyboard", Electronics, $59.99, 100 units, supplier 102.

Task 2: INSERT from SELECT

Copy all Electronics products to a new electronics_archive table.

Task 3: UPDATE with Calculation

Increase prices by 10% for all products in the Furniture category.

Task 4: UPDATE with JOIN

Set stock_quantity to 0 for all products that have been ordered more than 5 times.

Task 5: DELETE with Condition

Delete all products with stock_quantity = 0 and no orders.

Task 6: UPSERT (INSERT or UPDATE)

Insert a new product, or update price if product_id already exists.

Task 7: Transaction Management

Demonstrate a transaction that transfers stock between two products atomically.

💡 Hints

Hint 1: INSERT Syntax

```sql -- Specify columns (recommended) INSERT INTO table (col1, col2) VALUES (val1, val2);

-- All columns (risky if table structure changes) INSERT INTO table VALUES (val1, val2, val3);

-- Multiple rows INSERT INTO table (col1, col2) VALUES (val1a, val2a), (val1b, val2b);

</details>

<details>
<summary>Hint 2: UPDATE with JOIN</summary>
```sql
UPDATE table1
SET column = value
FROM table2
WHERE table1.id = table2.id AND condition;

(Syntax varies by database)

Hint 3: Transaction Syntax

```sql BEGIN TRANSACTION; -- SQL statements -- If error: ROLLBACK; COMMIT; ```

✅ Solutions

Click to reveal solutions

Solution 1: Basic INSERT

INSERT INTO products (product_id, product_name, category, price, stock_quantity, supplier_id)
VALUES (9, 'Wireless Keyboard', 'Electronics', 59.99, 100, 102);

-- Verify
SELECT * FROM products WHERE product_id = 9;

Result:

product_id | product_name       | category    | price | stock_quantity | supplier_id
-----------|--------------------| ------------|-------|----------------|------------
9          | Wireless Keyboard  | Electronics | 59.99 | 100            | 102

Best Practices:

• ✅ Always specify column names (explicit)
• ✅ Use meaningful values
• ✅ Verify foreign key constraints (supplier_id 102 exists)
• ❌ Don't rely on column order

Auto-increment ID (if supported):

INSERT INTO products (product_name, category, price, stock_quantity, supplier_id)
VALUES ('Wireless Keyboard', 'Electronics', 59.99, 100, 102);
-- product_id generated automatically

---

Solution 2: INSERT from SELECT

-- First, create archive table
CREATE TABLE electronics_archive AS
SELECT * FROM products WHERE 1=0;  -- Copy structure only

-- Insert data
INSERT INTO electronics_archive
SELECT * FROM products
WHERE category = 'Electronics';

-- Verify
SELECT COUNT(*) FROM electronics_archive;  -- Should be 5 (or 6 with new keyboard)

Alternative (create and insert in one step):

CREATE TABLE electronics_archive AS
SELECT * FROM products
WHERE category = 'Electronics';

Use Cases:

• Archiving old data
• Creating backup tables
• Populating staging tables
• Data migration

---

Solution 3: UPDATE with Calculation

-- Before: Check current prices
SELECT product_name, price FROM products WHERE category = 'Furniture';

-- Update: Increase by 10%
UPDATE products
SET price = price * 1.10
WHERE category = 'Furniture';

-- After: Verify changes
SELECT product_name, price FROM products WHERE category = 'Furniture';

Result:

-- Before
Office Chair    | 199.99
Standing Desk   | 499.99
Desk Lamp       | 45.99

-- After
Office Chair    | 219.99
Standing Desk   | 549.99
Desk Lamp       | 50.59

Best Practices:

• ✅ Always use WHERE clause (unless updating all rows intentionally)
• ✅ Test with SELECT first: SELECT price * 1.10 FROM products WHERE category = 'Furniture'
• ✅ Use transactions for critical updates
• ✅ Backup data before bulk updates

Common Mistake:

-- WRONG: Forgot WHERE clause - updates ALL products!
UPDATE products SET price = price * 1.10;

---

Solution 4: UPDATE with JOIN

-- Find products ordered more than 5 times
WITH high_demand_products AS (
  SELECT product_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY product_id
  HAVING COUNT(*) > 5
)
UPDATE products
SET stock_quantity = 0
WHERE product_id IN (SELECT product_id FROM high_demand_products);

-- Verify
SELECT p.product_name, p.stock_quantity, COUNT(o.order_id) AS order_count
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_id, p.product_name, p.stock_quantity
HAVING COUNT(o.order_id) > 5;

Alternative (database-specific syntax):

PostgreSQL:

UPDATE products p
SET stock_quantity = 0
FROM (
  SELECT product_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY product_id
  HAVING COUNT(*) > 5
) AS high_demand
WHERE p.product_id = high_demand.product_id;

MySQL:

UPDATE products p
JOIN (
  SELECT product_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY product_id
  HAVING COUNT(*) > 5
) AS high_demand ON p.product_id = high_demand.product_id
SET p.stock_quantity = 0;

---

Solution 5: DELETE with Condition

-- Find products to delete (safe check first)
SELECT p.product_id, p.product_name, p.stock_quantity
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
WHERE p.stock_quantity = 0 AND o.order_id IS NULL;

-- Delete (if safe)
DELETE FROM products
WHERE product_id IN (
  SELECT p.product_id
  FROM products p
  LEFT JOIN orders o ON p.product_id = o.product_id
  WHERE p.stock_quantity = 0 AND o.order_id IS NULL
);

-- Verify
SELECT COUNT(*) FROM products;

Safety Checklist:

• ✅ Always SELECT before DELETE
• ✅ Check foreign key constraints
• ✅ Use transactions for critical deletes
• ✅ Consider soft delete (status flag) instead of hard delete
• ✅ Backup data before bulk deletes

Soft Delete Pattern (Recommended for production):

-- Add deleted_at column
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMP;

-- "Delete" by setting timestamp
UPDATE products SET deleted_at = CURRENT_TIMESTAMP WHERE condition;

-- Query only active products
SELECT * FROM products WHERE deleted_at IS NULL;

---

Solution 6: UPSERT (INSERT or UPDATE)

-- SQLite/PostgreSQL: INSERT ... ON CONFLICT
INSERT INTO products (product_id, product_name, category, price, stock_quantity, supplier_id)
VALUES (1, 'Laptop Pro 15', 'Electronics', 1399.99, 30, 101)
ON CONFLICT (product_id) 
DO UPDATE SET 
  price = EXCLUDED.price,
  stock_quantity = EXCLUDED.stock_quantity;

-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO products (product_id, product_name, category, price, stock_quantity, supplier_id)
VALUES (1, 'Laptop Pro 15', 'Electronics', 1399.99, 30, 101)
ON DUPLICATE KEY UPDATE 
  price = VALUES(price),
  stock_quantity = VALUES(stock_quantity);

-- SQL Server: MERGE
MERGE INTO products AS target
USING (SELECT 1 AS product_id, 'Laptop Pro 15' AS product_name, 1399.99 AS price) AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
  UPDATE SET price = source.price
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, price) VALUES (source.product_id, source.product_name, source.price);

Use Cases:

• Syncing data from external sources
• Idempotent operations (can run multiple times safely)
• Maintaining cache tables

---

Solution 7: Transaction Management

-- Scenario: Transfer 10 units from product 2 to product 3
BEGIN TRANSACTION;

-- Step 1: Deduct from source
UPDATE products
SET stock_quantity = stock_quantity - 10
WHERE product_id = 2 AND stock_quantity >= 10;

-- Check if update succeeded
-- If affected rows = 0, rollback (insufficient stock)

-- Step 2: Add to destination
UPDATE products
SET stock_quantity = stock_quantity + 10
WHERE product_id = 3;

-- If any error occurred, rollback
-- Otherwise, commit
COMMIT;

-- Verify
SELECT product_id, product_name, stock_quantity
FROM products
WHERE product_id IN (2, 3);

With Error Handling (Pseudo-code):

BEGIN TRANSACTION;

UPDATE products SET stock_quantity = stock_quantity - 10
WHERE product_id = 2 AND stock_quantity >= 10;

IF @@ROWCOUNT = 0 THEN
  ROLLBACK;
  RAISE ERROR 'Insufficient stock';
END IF;

UPDATE products SET stock_quantity = stock_quantity + 10
WHERE product_id = 3;

COMMIT;

ACID Properties:

• Atomicity: All or nothing (both updates succeed or both fail)
• Consistency: Database remains in valid state
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist

🚀 Follow-up Challenges

1. Bulk INSERT: Insert 1000 products efficiently (use batch inserts)
2. Conditional UPDATE: Update price only if new price is higher
3. CASCADE DELETE: Delete supplier and all their products
4. Audit Trail: Create trigger to log all changes to products table

🔑 Key Takeaways

DML Operations Summary

Operation	Purpose	Syntax
INSERT	Add new rows	INSERT INTO table VALUES (...)
UPDATE	Modify existing rows	UPDATE table SET col = val WHERE ...
DELETE	Remove rows	DELETE FROM table WHERE ...
UPSERT	Insert or update	Database-specific (ON CONFLICT, MERGE)

Transaction Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	✅ Possible	✅ Possible	✅ Possible
READ COMMITTED	❌ Prevented	✅ Possible	✅ Possible
REPEATABLE READ	❌ Prevented	❌ Prevented	✅ Possible
SERIALIZABLE	❌ Prevented	❌ Prevented	❌ Prevented

Best Practices

Before Modifying Data:

• ✅ Backup critical data
• ✅ Test in development environment first
• ✅ Use transactions for multi-step operations
• ✅ SELECT before DELETE/UPDATE to verify scope
• ✅ Check foreign key constraints

During Modification:

• ✅ Always use WHERE clause (unless intentionally updating all)
• ✅ Use explicit column names in INSERT
• ✅ Validate data before inserting
• ✅ Handle errors gracefully

After Modification:

• ✅ Verify changes with SELECT
• ✅ Check row count affected
• ✅ Monitor for constraint violations
• ✅ Log important changes

Performance Tips

• Batch inserts instead of individual INSERTs
• Disable indexes during bulk loads (re-enable after)
• Use TRUNCATE instead of DELETE for entire table (faster, but can't rollback)
• Partition large UPDATEs into smaller batches
• Use appropriate isolation level (lower = faster, but less safe)

📚 Common Patterns

Pattern 1: Conditional Insert

-- Insert only if not exists
INSERT INTO products (product_name, price)
SELECT 'New Product', 99.99
WHERE NOT EXISTS (
  SELECT 1 FROM products WHERE product_name = 'New Product'
);

Pattern 2: Update with Subquery

-- Update based on aggregated data
UPDATE products p
SET price = (
  SELECT AVG(price) 
  FROM products 
  WHERE category = p.category
)
WHERE price IS NULL;

Pattern 3: Delete Duplicates (Keep First)

DELETE FROM products
WHERE product_id NOT IN (
  SELECT MIN(product_id)
  FROM products
  GROUP BY product_name, category
);

Pattern 4: Incremental Update

-- Update in batches to avoid locking
UPDATE products
SET processed = TRUE
WHERE product_id IN (
  SELECT product_id FROM products
  WHERE processed = FALSE
  LIMIT 1000
);

⚠️ Common Pitfalls

⚠️ Cạm bẫy

Pitfall 1: Forgetting WHERE Clause

-- DISASTER: Updates ALL rows!
UPDATE products SET price = 0;

-- CORRECT: Always use WHERE
UPDATE products SET price = 0 WHERE product_id = 1;

⚠️ Cạm bẫy

Pitfall 2: Foreign Key Violations

-- ERROR: Can't delete supplier with products
DELETE FROM suppliers WHERE supplier_id = 101;

-- CORRECT: Delete products first, or use CASCADE
DELETE FROM products WHERE supplier_id = 101;
DELETE FROM suppliers WHERE supplier_id = 101;

⚠️ Cạm bẫy

Pitfall 3: Not Using Transactions

-- WRONG: If second UPDATE fails, first succeeds (inconsistent state)
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- CORRECT: Use transaction
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

---

📚 Bài liên quan

Related Topics

• ← Exercise 6: CTEs
• → Exercise 8: Query Optimization
• SQL DML Guide