Chapter 6

INSERT, UPDATE & DELETE

Master the art of modifying data! Learn how to add, update, and remove data safely and efficiently while avoiding common pitfalls.

Data Modification Overview

So far, we've learned how to read data using SELECT. Now it's time to learn how to modify data: adding new records, updating existing ones, and removing data we no longer need.

Real-Life Analogy: Your Contact Book

Think of a database table like a physical contact book:

  • INSERT: Writing a new contact entry
  • UPDATE: Changing a phone number or address
  • DELETE: Erasing a contact completely
  • SELECT: Looking up information (what we've been doing)

Just like you need to be careful when using an eraser in your contact book, we need to be extremely careful with UPDATE and DELETE!

INSERT

Add new data to tables

Safe: Can't accidentally destroy existing data

UPDATE

Modify existing data

Caution: Can change many rows at once!

DELETE

Remove data permanently

Danger: Can't be undone without backups!

TRANSACTIONS

Group changes safely

Safety: Roll back if something goes wrong

🔍 Behind the Scenes: How Data Modification Works

When you modify data, here's what happens:

  1. Parse: Database checks your SQL syntax
  2. Validate: Checks constraints (NOT NULL, UNIQUE, etc.)
  3. Lock: Locks affected rows to prevent conflicts
  4. Execute: Makes the actual changes
  5. Log: Records changes for recovery (if transactions enabled)
  6. Unlock: Releases locks, makes changes visible

This is why modifying data is slower than reading it!

INSERT - Adding Single Rows

The INSERT statement adds new records to a table. It's the safest modification operation because you can't accidentally destroy existing data!

Basic INSERT Syntax:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
  • List the columns you want to fill
  • Provide values in the same order
  • Use quotes for strings, no quotes for numbers
  • NULL for missing values

Try It: Insert a New Employee

Add a new employee to the employees table:

Output will appear here...

Try It: Insert Without Specifying Columns

If you provide values for ALL columns in order, you can omit the column list:

Output will appear here...

🔍 Why Specifying Columns is Better

-- BETTER: Explicit columns
INSERT INTO employees (name, email, department_id, salary)
VALUES ('Uma White', 'uma@company.com', 3, 82000);

-- RISKY: No column list (must match ALL 7 columns in exact order!)
INSERT INTO employees
VALUES (NULL, 'Uma White', 'uma@company.com', 3, 82000, NULL, NULL);

-- What if someone adds a new column to the table?
-- Your second query will break!
-- The first query will still work.

Best Practice: Always specify column names! It makes your code self-documenting and resistant to table structure changes.

Try It: Inserting with NULL and DEFAULT Values

Output will appear here...

Real-World Examples: Different Data Types

-- String values (use quotes)
INSERT INTO customers (name, email)
VALUES ('John Doe', 'john@example.com');

-- Numbers (no quotes)
INSERT INTO orders (customer_id, total, quantity)
VALUES (1, 299.99, 5);

-- Dates (use quotes, format: 'YYYY-MM-DD')
INSERT INTO events (event_name, event_date)
VALUES ('Conference', '2024-06-15');

-- Boolean values (1 for TRUE, 0 for FALSE)
INSERT INTO users (username, is_active)
VALUES ('alice123', 1);

-- NULL values
INSERT INTO products (name, description, price)
VALUES ('Widget', NULL, 19.99);

INSERT - Multiple Rows at Once

Instead of running multiple INSERT statements, you can insert many rows in a single command. This is much faster!

Multiple Row INSERT Syntax:

INSERT INTO table_name (column1, column2)
VALUES
    (value1a, value2a),
    (value1b, value2b),
    (value1c, value2c);

Separate each row with a comma. Much more efficient than multiple INSERT statements!

Try It: Insert Multiple Employees

Output will appear here...

🔍 Performance: One vs Many INSERT Statements

-- SLOW: 1000 separate INSERTs
-- Each one has overhead (parse, validate, lock, unlock)
INSERT INTO products (name, price) VALUES ('Product1', 10.00);
INSERT INTO products (name, price) VALUES ('Product2', 20.00);
-- ... 998 more times ...

-- FAST: Single INSERT with 1000 rows
INSERT INTO products (name, price) VALUES
    ('Product1', 10.00),
    ('Product2', 20.00),
    ('Product3', 30.00),
    -- ... 997 more rows ...
    ('Product1000', 10000.00);

-- Result: Can be 10x to 100x faster!

Try It: Insert Projects with Various Statuses

Output will appear here...

INSERT from SELECT - Copy Data Between Tables

You can INSERT data from one table into another using a SELECT statement. Perfect for archiving, copying, or creating summary tables!

INSERT ... SELECT Syntax:

INSERT INTO target_table (column1, column2)
SELECT column1, column2
FROM source_table
WHERE condition;
  • No VALUES keyword needed
  • SELECT can include calculations, JOINs, etc.
  • Can insert thousands of rows instantly
  • Columns must match in number and type

Try It: Copy High-Salary Employees to Archive

Create an archive table and copy high earners:

Output will appear here...

Try It: Create Summary Table with Aggregations

Output will appear here...

Real-World Use Cases for INSERT ... SELECT

-- 1. Archive old orders
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < DATE('now', '-1 year');

-- 2. Create a backup table
INSERT INTO employees_backup
SELECT * FROM employees;

-- 3. Generate reports table
INSERT INTO monthly_sales_report (month, total_sales, order_count)
SELECT
    STRFTIME('%Y-%m', order_date) as month,
    SUM(total) as total_sales,
    COUNT(*) as order_count
FROM orders
GROUP BY STRFTIME('%Y-%m', order_date);

-- 4. Copy users to a new system
INSERT INTO new_system_users (username, email, created_date)
SELECT username, email, registration_date
FROM old_system_users
WHERE is_active = 1;

UPDATE - Modifying Existing Data

UPDATE changes existing data in a table. DANGER: Without a WHERE clause, UPDATE changes every single row!

UPDATE Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
  • SET: Specifies which columns to change
  • WHERE: Determines which rows to update
  • ⚠️ WARNING: Forgetting WHERE updates ALL rows!

Real-Life Analogy: Find and Replace

UPDATE is like "Find and Replace" in a word processor:

  • WHERE clause: The "Find" part (which rows?)
  • SET clause: The "Replace" part (change to what?)
  • No WHERE: Like replacing text in EVERY document on your computer!

Try It: Update a Single Employee's Salary

Output will appear here...

Try It: Update Multiple Columns at Once

Output will appear here...

Try It: Update Using Calculations

You can use the current value in your update:

Output will appear here...

🔍 UPDATE Execution Order

Understanding how UPDATE works:

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

-- Step 1: Find all rows matching WHERE (department = 'Engineering')
-- Step 2: For EACH matched row, calculate new salary
-- Step 3: Update each row with new value
-- Step 4: Return number of rows affected

-- Important: Each row is updated independently
-- The update of one row doesn't affect others

UPDATE Multiple Rows with WHERE

The real power of UPDATE is changing many rows at once. But this power comes with great responsibility!

Try It: Update All Projects with Specific Status

Output will appear here...

Try It: Conditional Updates with Complex WHERE

Output will appear here...

Try It: Update Based on Date

Output will appear here...

Real-World UPDATE Scenarios

-- 1. Mark old orders as archived
UPDATE orders
SET status = 'Archived'
WHERE order_date < DATE('now', '-2 years')
  AND status = 'Completed';

-- 2. Apply discount to specific product category
UPDATE products
SET price = price * 0.90
WHERE category = 'Electronics'
  AND price > 100;

-- 3. Update customer tier based on spending
UPDATE customers
SET tier = 'Gold'
WHERE total_spent >= 10000
  AND tier != 'Gold';

-- 4. Expire old passwords (security)
UPDATE users
SET password_expired = 1
WHERE last_password_change < DATE('now', '-90 days');

-- 5. Normalize phone numbers
UPDATE contacts
SET phone = REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', '')
WHERE phone LIKE '%(%';

DELETE - Removing Data

DELETE permanently removes rows from a table. EXTREME DANGER: Deleted data cannot be recovered without a backup!

DELETE Syntax:

DELETE FROM table_name
WHERE condition;
  • WHERE: Specifies which rows to delete
  • ⚠️⚠️⚠️ CRITICAL WARNING: Forgetting WHERE deletes EVERYTHING!
  • No UNDO: Once deleted, data is gone forever (unless you have backups)

Real-Life Analogy: Shredding Documents

Think of DELETE like putting documents through a shredder:

  • WHERE clause: Which documents to shred
  • Without WHERE: Shredding your ENTIRE filing cabinet!
  • No undo: Once shredded, you can't unshred
  • Backups: Like keeping photocopies in a safe

Try It: Delete a Single Row

Output will appear here...

Try It: Delete Multiple Rows with Condition

Output will appear here...

Try It: Delete with Complex Conditions

Output will appear here...

🔍 DELETE vs TRUNCATE vs DROP

-- DELETE: Remove specific rows
DELETE FROM employees WHERE department = 'Sales';
-- ✓ Can use WHERE clause
-- ✓ Can be rolled back (with transactions)
-- ✗ Slower for large deletions

-- TRUNCATE: Remove ALL rows (SQLite doesn't support this)
TRUNCATE TABLE employees;
-- ✓ Very fast
-- ✗ Cannot use WHERE
-- ✗ Cannot be rolled back in some databases

-- DROP: Delete the entire table structure
DROP TABLE employees;
-- ✗ Destroys the table itself
-- ✗ All data AND structure gone
-- ✗ Cannot be rolled back

DELETE Safety - Avoiding Disasters

DELETE is the most dangerous SQL command. Let's learn how to use it safely!

⚠️ Common Disaster

Forgetting the WHERE clause

DELETE FROM employees;

Result: ALL employees deleted!

🛡️ Safety Check

Always SELECT before DELETE

SELECT * WHERE condition;

Verify: These are the rows to delete

✅ Best Practice

Use transactions for safety

BEGIN; DELETE...; ROLLBACK;

Benefit: Test before committing

💾 Ultimate Safety

Regular backups

Daily database backups

Recovery: Restore if disaster strikes

The Safe DELETE Process (5 Steps):

-- STEP 1: COUNT how many rows will be affected
SELECT COUNT(*) FROM employees WHERE department = 'Temporary';

-- STEP 2: SELECT and VIEW the actual rows
SELECT * FROM employees WHERE department = 'Temporary';

-- STEP 3: If using transactions, start one
BEGIN TRANSACTION;

-- STEP 4: Perform the DELETE
DELETE FROM employees WHERE department = 'Temporary';

-- STEP 5: Verify, then COMMIT or ROLLBACK
SELECT * FROM employees WHERE department = 'Temporary'; -- Should be empty
COMMIT; -- Or ROLLBACK if something looks wrong!

Try It: Safe Deletion Process

Output will appear here...

Real-World DELETE Horror Stories (and Solutions)

-- ❌ HORROR STORY 1: Deleted all customers
DELETE FROM customers;  -- Forgot WHERE!
-- Solution: Always use WHERE, test with SELECT first

-- ❌ HORROR STORY 2: Wrong WHERE clause
DELETE FROM orders WHERE customer_id = 123;
-- Meant to delete order_id = 123, deleted all orders for customer 123!
-- Solution: Double-check your WHERE condition

-- ❌ HORROR STORY 3: Deleted production data instead of test data
DELETE FROM users WHERE email LIKE '%test%';
-- Deleted customer named "Preston"!
-- Solution: Use transactions, test in development first

-- ✅ CORRECT APPROACH:
-- 1. Backup database
-- 2. Test query in development
-- 3. Use transaction
BEGIN;
DELETE FROM old_records WHERE created_date < '2020-01-01';
-- 4. Verify result
SELECT COUNT(*) FROM old_records WHERE created_date < '2020-01-01';
-- 5. Commit only if correct
COMMIT;

Transactions - The Safety Net

Transactions let you group multiple changes together and roll them back if something goes wrong. Think of it as an "undo" button for your database!

Transaction Basics:

BEGIN TRANSACTION;  -- Or just BEGIN
    -- Your INSERT, UPDATE, DELETE statements
    -- Multiple statements can go here
COMMIT;  -- Save all changes (make them permanent)

-- OR

BEGIN TRANSACTION;
    -- Your statements
ROLLBACK;  -- Undo all changes (discard them)
  • BEGIN: Start a transaction
  • COMMIT: Save all changes permanently
  • ROLLBACK: Undo all changes, restore original state

Real-Life Analogy: Draft Mode

Transactions are like working on a draft:

  • BEGIN: Start working on a draft document
  • Make changes: Edit, delete, add new content
  • Review: Check if changes look good
  • COMMIT: Save draft as final version (publish)
  • ROLLBACK: Discard draft, keep original version

Try It: Transaction with COMMIT

Output will appear here...

Try It: Transaction with ROLLBACK

Output will appear here...

Try It: Multi-Step Transaction

Transfer budget between projects atomically:

Output will appear here...

🔍 ACID Properties of Transactions

Good databases guarantee ACID properties:

  • Atomicity: All changes succeed or all fail (no partial updates)
  • Consistency: Database stays in valid state (constraints enforced)
  • Isolation: Transactions don't interfere with each other
  • Durability: Committed changes survive crashes/power loss

This is why transactions are so important for data integrity!

Real-World Transaction Examples

-- Bank transfer (both must succeed or both must fail)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- Order processing
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (123, 99.99);
UPDATE products SET stock = stock - 1 WHERE product_id = 456;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 456, 1);
COMMIT;

-- Batch updates with safety
BEGIN;
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';
-- Check if result looks reasonable
SELECT AVG(salary) FROM employees WHERE department = 'Engineering';
-- If too high: ROLLBACK, if good: COMMIT
COMMIT;

Safety Best Practices

Follow these practices to avoid data disasters and become a responsible database user!

1. Always Test First

Run SELECT before UPDATE/DELETE

-- Do this first:
SELECT * FROM table
WHERE condition;

-- Then if correct:
DELETE FROM table
WHERE condition;

2. Use Transactions

Wrap dangerous operations

BEGIN;
DELETE FROM old_data
WHERE date < '2020-01-01';
-- Verify, then:
COMMIT;

3. Backup Regularly

Your ultimate safety net

-- Daily backups
-- Test restore process
-- Keep multiple versions
-- Store off-site

4. Be Specific

Always use WHERE clauses

-- Dangerous:
UPDATE employees
SET salary = 50000;

-- Safe:
UPDATE employees
SET salary = 50000
WHERE id = 123;

5. Test in Development

Never test on production first

-- 1. Test in dev database
-- 2. Review results
-- 3. Then run in production
-- 4. With a backup ready!

6. Use Version Control

Track all database changes

-- Save migration scripts
-- Document all changes
-- Code review for database
-- Able to rollback

Pre-Flight Checklist for Data Modifications

Before running UPDATE or DELETE:

☐ 1. Do I have a recent backup?
☐ 2. Have I tested this query with SELECT?
☐ 3. Did I include a WHERE clause?
☐ 4. Is this in a transaction I can rollback?
☐ 5. Have I tested in development first?
☐ 6. Do I understand exactly which rows will be affected?
☐ 7. Am I running this on the correct database/table?
☐ 8. Have I double-checked my WHERE condition?
☐ 9. Do I have authorization to make this change?
☐ 10. Is someone available to help if something goes wrong?

If you can't check ALL boxes, STOP and reconsider!

Common Mistakes and How to Avoid Them

-- MISTAKE 1: Forgetting WHERE
UPDATE employees SET salary = 100000;  -- Everyone gets same salary!
-- FIX: Always include WHERE
UPDATE employees SET salary = 100000 WHERE id = 5;

-- MISTAKE 2: Wrong comparison operator
DELETE FROM orders WHERE total = 0;  -- Only deletes exactly 0
DELETE FROM orders WHERE total <= 0;  -- Better: negative too
-- FIX: Think about edge cases

-- MISTAKE 3: String matching errors
DELETE FROM users WHERE email = 'test';  -- Doesn't match 'test@example.com'
DELETE FROM users WHERE email LIKE '%test%';  -- Better: partial match
-- FIX: Test your LIKE patterns

-- MISTAKE 4: Not checking affected rows
UPDATE products SET price = price * 2 WHERE category = 'Electroncs';  -- Typo!
-- Returns "0 rows affected" but you don't notice!
-- FIX: Always check row count returned

-- MISTAKE 5: Assuming transaction without beginning one
DELETE FROM old_data WHERE year < 2020;  -- Can't rollback!
-- FIX: Explicitly BEGIN transaction

Practice Exercises

Time to practice modifying data safely! Remember: test with SELECT first!

Guided Exercise 1: Employee Management

Follow the steps to manage employee records:

Output will appear here...

Challenge 1: Project Budget Reallocation

Task: You need to:

  • Insert 3 new projects (any names, budgets $50k-$150k)
  • Increase all project budgets by 15% for Active projects
  • Delete all projects with budget less than $60,000
  • Show final project list ordered by budget
Output will appear here...

Challenge 2: Safe Deletion with Transaction

Task: Use a transaction to safely delete old projects:

  • Start a transaction
  • Delete all 'Completed' projects
  • Verify the deletion
  • Commit if correct, or show how you would rollback
Output will appear here...

Challenge 3: Data Archive

Task: Archive old employee data:

  • Create a table called employees_archive (same structure as employees)
  • Copy all employees with salary less than $70,000 to the archive
  • Delete those employees from the main table
  • Show counts in both tables
Output will appear here...

Knowledge Check Quiz

Test your understanding of data modification!

1. What happens if you run UPDATE without a WHERE clause?

2. Which is the safest way to test a DELETE before running it?

3. What does ROLLBACK do in a transaction?

4. Which statement is true about INSERT ... SELECT?

5. What's the most important safety practice for UPDATE and DELETE?

6. How can you insert multiple rows in a single INSERT statement?

7. What's the difference between COMMIT and ROLLBACK?

8. When updating salary, which syntax is correct to give a 10% raise?

← Previous Chapter Next Chapter →