🗃️ Data Modification: Your Magic Wand for Changing Data

Imagine your database is a giant toy box. You can add new toys (INSERT), fix broken toys (UPDATE), or throw away old toys (DELETE). Let’s learn how to use these magic powers!

🎯 The Big Picture

Think of a database table like a class attendance sheet. You can:

• ✏️ Add a new student’s name (INSERT)
• 🔧 Change a student’s phone number (UPDATE)
• 🗑️ Remove a student who left (DELETE)
• 🔄 Add OR Change in one go (MERGE)

graph TD
    A[Your Data Table] --> B[INSERT: Add New Rows]
    A --> C[UPDATE: Change Existing Rows]
    A --> D[DELETE: Remove Rows]
    A --> E[MERGE: Add or Update]

📥 INSERT Statement: Adding New Data

What is INSERT?

INSERT is like writing a new name on your class list. You’re adding a brand new row to your table!

Simple Example

Imagine you have a students table:

To add a new student:

INSERT INTO students (id, name, age)
VALUES (2, 'Maya', 9);

Result: Now Maya is in your table!

🧠 Remember This!

• INSERT INTO = which table
• Column names in parentheses
• VALUES = the actual data

📦 INSERT Single vs Multi Rows

Single Row (One at a Time)

Like adding one cookie to the jar:

INSERT INTO cookies (flavor)
VALUES ('chocolate');

Multiple Rows (Many at Once)

Like adding a whole box of cookies:

INSERT INTO cookies (flavor)
VALUES
  ('chocolate'),
  ('vanilla'),
  ('strawberry');

Why use multi-row? It’s faster! One trip to the database instead of three.

graph TD
    A[Single INSERT] --> B[1 Trip to Database]
    C[Multi-Row INSERT] --> D[Still Just 1 Trip!]
    D --> E[Much Faster! ⚡]

🔄 INSERT with SELECT

The Magic Copier

What if you want to copy data from one table to another? INSERT with SELECT is like a photocopy machine for your data!

Example: Copying Honor Students

INSERT INTO honor_roll (name, grade)
SELECT name, grade
FROM students
WHERE grade >= 90;

What happens:

1. SELECT finds all students with grade 90+
2. INSERT copies them to honor_roll table

Real-Life Use

Imagine moving all completed orders to an archive:

INSERT INTO orders_archive
SELECT * FROM orders
WHERE status = 'completed';

🔧 UPDATE Statement: Fixing Your Data

What is UPDATE?

UPDATE is like using an eraser and pencil. You find something wrong and fix it!

Simple Example

Alex’s age is wrong. Let’s fix it:

UPDATE students
SET age = 11
WHERE name = 'Alex';

Before: Alex, age 10 After: Alex, age 11

⚠️ Golden Rule

ALWAYS use WHERE! Without it, you’ll change EVERYONE:

-- DANGEROUS! Changes ALL students!
UPDATE students SET age = 11;

-- SAFE! Changes only Alex
UPDATE students SET age = 11
WHERE name = 'Alex';

🎯 UPDATE with Conditions

Updating Multiple Columns

You can fix several things at once:

UPDATE students
SET age = 11,
    grade = 'A'
WHERE name = 'Alex';

Using Different Conditions

Update based on numbers:

UPDATE products
SET price = price * 0.9
WHERE price > 100;

This gives 10% off to expensive items!

Update based on dates:

UPDATE tasks
SET status = 'overdue'
WHERE due_date < '2024-01-01';

Multiple Conditions with AND/OR

UPDATE employees
SET bonus = 500
WHERE department = 'Sales'
  AND years_worked >= 5;

🗑️ DELETE Statement: Removing Data

What is DELETE?

DELETE is like throwing away old papers. The row is gone forever!

Simple Example

DELETE FROM students
WHERE name = 'Maya';

Maya is now removed from the table.

⚠️ Super Important!

ALWAYS use WHERE! Without it, you delete EVERYTHING:

-- DANGEROUS! Deletes ALL students!
DELETE FROM students;

-- SAFE! Deletes only Maya
DELETE FROM students
WHERE name = 'Maya';

Pro Tip: Test First!

Before deleting, check what you’ll remove:

-- First, see what will be deleted
SELECT * FROM students WHERE name = 'Maya';

-- Then delete if it looks right
DELETE FROM students WHERE name = 'Maya';

💥 DELETE vs TRUNCATE vs DROP

The Three Ways to Remove Data

Think of your table as a notebook:

graph TD
    A[DELETE] --> B[Removes Some Rows]
    A --> C[Can use WHERE]
    A --> D[Can Undo with Rollback]

    E[TRUNCATE] --> F[Removes ALL Rows]
    E --> G[Cannot use WHERE]
    E --> H[Very Fast!]

    I[DROP] --> J[Removes Entire Table]
    I --> K[Table Structure Gone]
    I --> L[Cannot Get Back!]

When to Use Each?

DELETE:

DELETE FROM orders WHERE year = 2020;

Remove old orders but keep recent ones

TRUNCATE:

TRUNCATE TABLE temp_data;

Clear a table completely (faster than DELETE)

DROP:

DROP TABLE old_backup;

Remove a table you don’t need anymore

Key Differences

🔄 MERGE Statement (Upsert)

What is MERGE?

MERGE is the superhero of data modification! It can:

• Insert if the row doesn’t exist
• Update if the row already exists

This is called “UPSERT” (Update + Insert)!

The Problem MERGE Solves

Imagine you get a daily list of product prices. Some products are new, some have updated prices.

Without MERGE: You’d need to:

1. Check if product exists
2. If yes → UPDATE
3. If no → INSERT

With MERGE: One statement does it all!

Simple Example

MERGE INTO products AS target
USING new_prices AS source
ON target.product_id = source.product_id

WHEN MATCHED THEN
  UPDATE SET price = source.price

WHEN NOT MATCHED THEN
  INSERT (product_id, price)
  VALUES (source.product_id, source.price);

Breaking It Down

graph TD
    A[MERGE starts] --> B{Does row exist?}
    B -->|Yes, MATCHED| C[UPDATE the row]
    B -->|No, NOT MATCHED| D[INSERT new row]
    C --> E[Done!]
    D --> E

Real-Life Example

Scenario: You have a customer rewards table. Daily, you get points updates.

MERGE INTO rewards AS r
USING daily_points AS d
ON r.customer_id = d.customer_id

WHEN MATCHED THEN
  UPDATE SET
    points = r.points + d.points,
    last_update = GETDATE()

WHEN NOT MATCHED THEN
  INSERT (customer_id, points, last_update)
  VALUES (d.customer_id, d.points, GETDATE());

🎉 Quick Summary

🌟 Your Journey So Far

You’ve learned the four superpowers of data modification:

1. INSERT - Add new data (single, multiple, or from SELECT)
2. UPDATE - Change existing data (always use WHERE!)
3. DELETE - Remove data (carefully!)
4. MERGE - The smart way to add OR update

Remember: With great power comes great responsibility. Always test your commands before running them on important data!

🎯 Pro Tip: Always backup your data before making big changes. It’s like saving your game before the boss fight!