Lesson 5: Data Modification — INSERT, UPDATE, DELETE 💾

Introduction

So far, you've been reading data from databases. But databases aren't just libraries—they're living documents that need updates. In this lesson, you'll learn the three core operations that change data: INSERT (adding new rows), UPDATE (modifying existing rows), and DELETE (removing rows). You'll also discover how to use the RETURNING clause to see what changed and learn transaction basics to keep your data safe.

Think of a database table like a restaurant's reservation book. You need to:

• ✏️ INSERT: Add new reservations
• 📝 UPDATE: Change a party size or time
• 🗑️ DELETE: Cancel reservations
• 🔐 TRANSACTIONS: Make sure all related changes happen together (or not at all)

Core Concepts

1. INSERT — Adding New Rows 📥

The INSERT statement adds new rows to a table. There are several patterns:

Basic INSERT syntax:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Key points:

• List the columns you're filling (good practice, even if inserting all)
• Values must match column order and data types
• Columns with DEFAULT values or NULL allowed can be omitted
• Primary keys often auto-generate (SERIAL/AUTO_INCREMENT)

+------------------+
| INSERT Operation |
+------------------+
     |
     v
[Specify Table]
     |
     v
[List Columns]
     |
     v
[Provide VALUES]
     |
     v
[New Row Added]

Multiple rows at once:

INSERT INTO products (name, price, category)
VALUES 
  ('Laptop', 999.99, 'Electronics'),
  ('Mouse', 29.99, 'Electronics'),
  ('Desk', 349.99, 'Furniture');

💡 Tip: Inserting multiple rows in one statement is much faster than separate INSERTs—it's a single database transaction.

INSERT from SELECT: You can insert results from a query:

INSERT INTO archived_orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date < '2020-01-01';

This pattern is powerful for:

• Creating backup tables
• Moving data between tables
• Generating reports that need to be saved

2. UPDATE — Modifying Existing Rows 🔄

The UPDATE statement changes data in existing rows. It's one of the most dangerous operations if used carelessly!

Basic UPDATE syntax:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

⚠️ CRITICAL: The WHERE clause is essential! Without it, you'll update every row in the table.

+----------------+
| UPDATE Process |
+----------------+
     |
     v
[Specify Table]
     |
     v
[SET new values]
     |
     v
[WHERE condition] ← FILTERS which rows to update
     |
     v
[Rows Modified]

Update with calculations:

-- Give 10% raise to employees in sales department
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

Update based on another table (with FROM or JOIN):

-- PostgreSQL style
UPDATE products
SET stock = stock - order_items.quantity
FROM order_items
WHERE products.product_id = order_items.product_id
  AND order_items.order_id = 12345;

💡 Tip: Always test your WHERE clause with a SELECT first:

-- Test: see which rows will be affected
SELECT * FROM employees WHERE department = 'Sales';

-- Then update
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Sales';

3. DELETE — Removing Rows 🗑️

The DELETE statement removes rows from a table.

Basic DELETE syntax:

DELETE FROM table_name
WHERE condition;

⚠️ CRITICAL: Like UPDATE, omitting WHERE deletes everything. This is often catastrophic!

Safe deletion pattern:

-- 1. First, SELECT to see what will be deleted
SELECT * FROM orders WHERE order_date < '2020-01-01';

-- 2. Verify the results, then DELETE
DELETE FROM orders WHERE order_date < '2020-01-01';

Delete with subquery:

-- Delete customers who have never placed an order
DELETE FROM customers
WHERE customer_id NOT IN (
  SELECT DISTINCT customer_id FROM orders
);

🧠 Mnemonic: "See before you Destroy" — always SELECT before DELETE.

Soft deletes (recommended pattern): Instead of actually deleting data, many applications use a "soft delete" with a flag:

-- Add a deleted_at column
ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMP;

-- "Delete" by setting timestamp
UPDATE customers 
SET deleted_at = CURRENT_TIMESTAMP 
WHERE customer_id = 42;

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

Why soft deletes?

• ✅ Can recover accidentally deleted data
• ✅ Maintains referential integrity
• ✅ Keeps audit trail
• ✅ Safer for production systems

4. RETURNING Clause — See What Changed 📋

The RETURNING clause (PostgreSQL, Oracle, SQLite 3.35+) shows you the data that was inserted, updated, or deleted. This is incredibly useful!

INSERT with RETURNING:

INSERT INTO customers (name, email)
VALUES ('Alice Johnson', 'alice@example.com')
RETURNING customer_id, name, created_at;

Result:

customer_id |     name       |       created_at
------------+----------------+------------------------
        142 | Alice Johnson  | 2024-01-15 10:30:45

💡 Why this matters: You immediately get the auto-generated ID without a separate SELECT query!

UPDATE with RETURNING:

UPDATE products
SET price = price * 0.9
WHERE category = 'Electronics'
RETURNING product_id, name, price;

You see exactly which products were updated and their new prices.

DELETE with RETURNING:

DELETE FROM orders
WHERE status = 'cancelled'
RETURNING order_id, customer_id, total_amount;

Perfect for logging what was deleted!

5. Transactions — All or Nothing 🔐

A transaction groups multiple SQL statements into a single unit. Either all succeed or all fail—there's no in-between.

Transaction commands:

BEGIN;           -- Start transaction (or BEGIN TRANSACTION)
COMMIT;          -- Save all changes
ROLLBACK;        -- Undo all changes

Why transactions matter: Imagine transferring money between bank accounts:

BEGIN;

-- Deduct from account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Add to account B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

If there's an error after the first UPDATE but before the second, the money would vanish! With a transaction:

• ✅ Both updates succeed together
• ✅ Or both fail, leaving data unchanged

+------------------+
|  TRANSACTION     |
+------------------+
| BEGIN            |
|   ↓              |
| [Statement 1]    |
|   ↓              |
| [Statement 2]    |
|   ↓              |
| [Statement 3]    |
|   ↓              |
| Error? -----> ROLLBACK (undo all)
|   ↓              |
| Success? ---> COMMIT (save all)
+------------------+

ROLLBACK for safety:

BEGIN;

-- Make changes
DELETE FROM orders WHERE customer_id = 42;

-- Changed your mind? Undo it!
ROLLBACK;

🤔 Did you know? Many databases use "autocommit" mode by default—each statement is its own transaction. BEGIN turns this off temporarily.

Examples

Example 1: Building a Product Catalog 🛒

Scenario: You're setting up an online store's database.

-- Create the products table
CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert initial products
INSERT INTO products (name, price, stock_quantity)
VALUES 
  ('Wireless Mouse', 24.99, 50),
  ('USB Cable', 9.99, 200),
  ('Laptop Stand', 39.99, 30)
RETURNING product_id, name;

Result:

product_id |     name
-----------+---------------
         1 | Wireless Mouse
         2 | USB Cable
         3 | Laptop Stand

Explanation:

• SERIAL PRIMARY KEY auto-generates unique IDs
• DEFAULT values fill in automatically if not provided
• RETURNING shows us the new product IDs immediately
• Inserting multiple rows at once is efficient

Example 2: Processing an Order with Transactions 📦

Scenario: A customer orders 5 wireless mice. We need to create an order record AND reduce stock.

BEGIN;

-- Create the order
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (42, CURRENT_TIMESTAMP, 124.95)
RETURNING order_id;  -- Let's say this returns 1001

-- Reduce stock
UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 1  -- Wireless Mouse
  AND stock_quantity >= 5;  -- Only if enough stock!

-- Check if update worked (affected rows > 0)
-- If yes: COMMIT. If no: ROLLBACK
COMMIT;

Explanation:

• BEGIN starts the transaction
• If stock is insufficient, the UPDATE affects 0 rows
• We can check affected rows in application code
• COMMIT saves both changes together
• If anything fails, ROLLBACK undoes everything

💡 Real-world tip: Application code should check the result of the UPDATE and ROLLBACK if stock was insufficient.

Example 3: Updating Prices with a Calculation 💰

Scenario: Holiday sale—20% off all items over $30.

-- First, see what will change
SELECT product_id, name, price, 
       ROUND(price * 0.8, 2) AS sale_price
FROM products
WHERE price > 30;

Result:

product_id |     name      | price | sale_price
-----------+---------------+-------+------------
         3 | Laptop Stand  | 39.99 |      31.99

-- Apply the discount
UPDATE products
SET price = ROUND(price * 0.8, 2)
WHERE price > 30
RETURNING product_id, name, price;

Explanation:

• Always SELECT first to preview changes
• ROUND(price * 0.8, 2) calculates 20% off, rounded to 2 decimals
• WHERE clause prevents updating cheaper items
• RETURNING confirms exactly what changed

Example 4: Safe Deletion with Archival 🗄️

Scenario: Delete old orders but keep a backup.

BEGIN;

-- Create archive table (if needed)
CREATE TABLE IF NOT EXISTS archived_orders AS
SELECT * FROM orders WHERE FALSE;  -- Same structure, no data

-- Copy old orders to archive
INSERT INTO archived_orders
SELECT * FROM orders
WHERE order_date < '2023-01-01';

-- Delete from main table
DELETE FROM orders
WHERE order_date < '2023-01-01'
RETURNING order_id, customer_id, order_date;

-- If everything looks good
COMMIT;

Explanation:

• Transaction ensures backup happens before deletion
• CREATE TABLE AS SELECT copies table structure
• INSERT INTO ... SELECT moves the data
• DELETE ... RETURNING shows what was removed
• If anything fails, ROLLBACK preserves original data

Common Mistakes ⚠️

1. Forgetting WHERE Clause

-- DANGER! Deletes ALL customers
DELETE FROM customers;

-- What you meant:
DELETE FROM customers WHERE customer_id = 42;

Prevention: Always write WHERE first, test with SELECT.

2. Wrong Data Types

-- Error: price is numeric, not text
INSERT INTO products (name, price)
VALUES ('Widget', 'twenty dollars');  -- ❌

-- Correct:
INSERT INTO products (name, price)
VALUES ('Widget', 20.00);  -- ✅

3. Violating Constraints

-- Error: customer_id 999 doesn't exist (foreign key violation)
INSERT INTO orders (customer_id, order_date)
VALUES (999, CURRENT_TIMESTAMP);

Check: Ensure referenced records exist before inserting.

4. Forgetting COMMIT

BEGIN;
INSERT INTO products (name, price) VALUES ('New Item', 49.99);
-- Oops, forgot COMMIT!
-- Changes disappear when connection closes

Rule: Every BEGIN needs a COMMIT (or ROLLBACK).

5. Updating Without Testing

-- Meant to update one product, updated 100!
UPDATE products SET price = 0.99
WHERE category = 'Electronics';  -- Too broad!

Prevention: SELECT first, verify row count, then UPDATE.

6. Column/Value Mismatch

-- Error: 3 columns, 2 values
INSERT INTO products (name, price, stock_quantity)
VALUES ('Mouse', 24.99);  -- ❌

-- Correct: match the count
INSERT INTO products (name, price, stock_quantity)
VALUES ('Mouse', 24.99, 50);  -- ✅

7. Using DELETE Instead of Soft Delete

-- Hard delete - data gone forever!
DELETE FROM customers WHERE customer_id = 42;

-- Better: soft delete
UPDATE customers 
SET active = FALSE, deleted_at = CURRENT_TIMESTAMP
WHERE customer_id = 42;

Key Takeaways 🎯

✅ INSERT adds new rows—use multiple VALUES for efficiency

✅ UPDATE modifies existing rows—always include WHERE clause

✅ DELETE removes rows—test with SELECT first

✅ RETURNING clause shows changed data immediately (PostgreSQL, SQLite 3.35+, Oracle)

✅ Transactions (BEGIN/COMMIT/ROLLBACK) ensure data integrity

✅ Safety pattern: SELECT → verify → BEGIN → modify → COMMIT

✅ Soft deletes are safer than hard deletes for production

✅ Test in development before running in production!

📚 Further Study

1. PostgreSQL INSERT Documentation: https://www.postgresql.org/docs/current/sql-insert.html
2. SQL Transaction Tutorial: https://www.sqlitetutorial.net/sqlite-transaction/
3. Database Transaction Isolation Levels: https://www.postgresql.org/docs/current/transaction-iso.html

📋 Quick Reference Card

+---------------------------+----------------------------------------+
| Command                   | Purpose                                |
+---------------------------+----------------------------------------+
| INSERT INTO               | Add new rows                           |
| INSERT ... VALUES         | Specify data to insert                 |
| INSERT ... SELECT         | Insert from query results              |
| UPDATE ... SET            | Modify existing rows                   |
| UPDATE ... WHERE          | Filter which rows to update            |
| DELETE FROM               | Remove rows                            |
| DELETE ... WHERE          | Filter which rows to delete            |
| RETURNING                 | Show inserted/updated/deleted data     |
| BEGIN                     | Start transaction                      |
| COMMIT                    | Save transaction changes               |
| ROLLBACK                  | Undo transaction changes               |
+---------------------------+----------------------------------------+

Safety Checklist:
□ SELECT before UPDATE/DELETE
□ Always use WHERE clause (unless intentionally affecting all rows)
□ Test with small datasets first
□ Use transactions for multi-step operations
□ Consider soft deletes for production
□ Check foreign key constraints
□ Verify COMMIT after BEGIN

---

🔧 Try this: Open your database and practice with a test table. Create it, insert data, update it, then ROLLBACK. Seeing changes disappear will make transactions click!

You now have the power to modify data safely. In the next lesson, we'll explore advanced filtering with HAVING, CASE statements, and window functions. But first, practice these commands—they're the foundation of database applications! 💪