INSERT, UPDATE, DELETE: Modifying Data Safely

Picture this: You've been hired as a data analyst at a growing e-commerce company. The sales team just handed you a spreadsheet with 500 new customer records that need to go into the database. Marketing discovered that 200 existing customers have outdated email addresses. And legal says you need to permanently remove all data for customers who requested deletion under privacy regulations.

This is real-world data work. Reading data is just the beginning—you need to modify it safely and confidently. By the end of this lesson, you'll handle these scenarios like a professional, knowing exactly how to add new records, update existing ones, and delete data without breaking anything.

What you'll learn:

• Insert single and multiple records into database tables
• Update existing records with new values, both individual fields and bulk changes
• Delete records safely using precise WHERE clauses
• Use transactions to group changes and roll back mistakes
• Apply best practices that prevent data disasters

Prerequisites

You should be comfortable with basic SQL SELECT statements and understand what database tables, columns, and rows are. If you can write SELECT * FROM customers WHERE city = 'Chicago', you're ready.

Understanding Data Modification Commands

Before we dive into specific commands, let's understand what we're working with. SQL provides three core commands for changing data:

• INSERT: Adds new rows to a table
• UPDATE: Changes values in existing rows
• DELETE: Removes rows from a table

Think of these like editing a spreadsheet, but with more precision and safety features. Unlike clicking and typing in Excel, SQL forces you to be explicit about exactly what you want to change.

Each command affects the permanent data in your database. There's no automatic "undo" button, which is why we'll emphasize safety practices throughout.

INSERT: Adding New Data

Basic INSERT Syntax

The INSERT statement adds new rows to a table. Here's the basic structure:

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

Let's work with a realistic example. Imagine you're managing a customers table with these columns:

• customer_id (integer, auto-increments)
• first_name (text)
• last_name (text)
• email (text)
• registration_date (date)
• city (text)

Here's how to add a new customer:

INSERT INTO customers (first_name, last_name, email, registration_date, city)
VALUES ('Sarah', 'Chen', 'sarah.chen@email.com', '2024-03-15', 'Seattle');

Notice we didn't include customer_id—it auto-increments, so the database assigns the next available number automatically.

Column Order Flexibility

You don't have to list columns in the same order they appear in the table. This works perfectly:

INSERT INTO customers (email, city, first_name, last_name, registration_date)
VALUES ('mike.torres@email.com', 'Austin', 'Mike', 'Torres', '2024-03-15');

The key is that the VALUES must match the column order you specified, not the table's original column order.

Inserting Multiple Rows

Rather than writing separate INSERT statements for each record, you can insert multiple rows at once:

INSERT INTO customers (first_name, last_name, email, registration_date, city)
VALUES 
    ('Jennifer', 'Wu', 'j.wu@email.com', '2024-03-15', 'Portland'),
    ('David', 'Martinez', 'david.m@email.com', '2024-03-15', 'Denver'),
    ('Lisa', 'Johnson', 'lisa.j@email.com', '2024-03-15', 'Phoenix');

This is much more efficient than three separate INSERT statements, especially when dealing with hundreds or thousands of records.

Handling Special Values

Sometimes you need to insert special values:

NULL values (when data is missing or unknown):

INSERT INTO customers (first_name, last_name, email, registration_date, city)
VALUES ('Alex', 'Smith', 'alex@email.com', '2024-03-15', NULL);

Current date/time:

INSERT INTO customers (first_name, last_name, email, registration_date, city)
VALUES ('Maya', 'Patel', 'maya@email.com', CURRENT_DATE, 'Miami');

Tip: Different databases use different functions for current date/time. CURRENT_DATE works in PostgreSQL and many others, but SQL Server uses GETDATE() and MySQL uses NOW().

UPDATE: Modifying Existing Data

Basic UPDATE Syntax

The UPDATE statement changes values in existing rows. The basic structure is:

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

The WHERE clause is crucial—it determines which rows get updated. Let's see this in action.

Single Row Updates

Say customer Sarah Chen got married and changed her last name:

UPDATE customers
SET last_name = 'Chen-Williams'
WHERE customer_id = 101;

This updates exactly one row—the customer with ID 101. Always use a unique identifier like customer_id when updating a single record.

Multiple Column Updates

You can update multiple columns in one statement:

UPDATE customers
SET last_name = 'Chen-Williams', 
    email = 'sarah.williams@email.com'
WHERE customer_id = 101;

Bulk Updates

Sometimes you need to update many rows based on a condition. Let's say your company expanded and all customers in "Portland" should now be listed as "Portland, OR":

UPDATE customers
SET city = 'Portland, OR'
WHERE city = 'Portland';

This updates every row where the city is exactly 'Portland'.

Updates Based on Calculations

You can update columns based on their current values or calculations. If you had a total_purchases column and wanted to add a $50 bonus to customers in Seattle:

UPDATE customers
SET total_purchases = total_purchases + 50.00
WHERE city = 'Seattle';

Conditional Updates with CASE

For more complex logic, use CASE statements. Let's assign customer tiers based on total purchases:

UPDATE customers
SET customer_tier = 
    CASE 
        WHEN total_purchases >= 1000 THEN 'Gold'
        WHEN total_purchases >= 500 THEN 'Silver'
        ELSE 'Bronze'
    END
WHERE customer_tier IS NULL;

This updates only customers who don't already have a tier assigned.

Warning: Never run UPDATE without WHERE unless you really want to update every single row in the table. UPDATE customers SET city = 'Chicago' would set every customer's city to Chicago!

DELETE: Removing Data

Basic DELETE Syntax

The DELETE statement removes entire rows from a table:

DELETE FROM table_name
WHERE condition;

Single Row Deletion

To delete a specific customer:

DELETE FROM customers
WHERE customer_id = 205;

Conditional Deletion

Delete all customers who haven't made a purchase and registered over a year ago:

DELETE FROM customers
WHERE total_purchases = 0 
  AND registration_date < '2023-01-01';

Bulk Deletion

Remove all test data (assuming test accounts have emails ending in '@test.com'):

DELETE FROM customers
WHERE email LIKE '%@test.com';

Critical Warning: DELETE is permanent. DELETE FROM customers with no WHERE clause deletes every customer in your database. Always double-check your WHERE condition!

The WHERE Clause: Your Safety Net

The WHERE clause is your most important tool for safe data modification. It determines exactly which rows get affected. Let's explore how to write precise WHERE conditions.

Exact Matches

-- Single value
WHERE customer_id = 101
WHERE city = 'Seattle'

-- Multiple possible values
WHERE city IN ('Seattle', 'Portland', 'San Francisco')

Pattern Matching

-- Emails from Gmail
WHERE email LIKE '%@gmail.com'

-- Names starting with 'A'
WHERE first_name LIKE 'A%'

-- Phone numbers with specific area code
WHERE phone LIKE '206-%'

Range Conditions

-- Recent registrations
WHERE registration_date >= '2024-01-01'

-- Purchase amount range
WHERE total_purchases BETWEEN 100 AND 500

-- Exclude specific values
WHERE city NOT IN ('Test City', 'Demo Location')

Combining Conditions

-- Multiple conditions (all must be true)
WHERE city = 'Seattle' 
  AND registration_date >= '2024-01-01' 
  AND email IS NOT NULL

-- Either condition can be true
WHERE city = 'Seattle' OR city = 'Portland'

-- Complex combinations
WHERE (city = 'Seattle' OR city = 'Portland') 
  AND total_purchases > 200

Testing Your WHERE Clause

Before running UPDATE or DELETE, always test your WHERE clause with SELECT:

-- Test which rows will be affected
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE registration_date < '2023-01-01' AND total_purchases = 0;

-- If this looks right, then run the actual DELETE
DELETE FROM customers
WHERE registration_date < '2023-01-01' AND total_purchases = 0;

Transactions: Your Undo Button

Transactions let you group multiple changes together and roll them back if something goes wrong. Think of it as a safety checkpoint system.

Basic Transaction Syntax

BEGIN TRANSACTION;  -- Start the transaction

-- Your INSERT, UPDATE, DELETE statements here
INSERT INTO customers (first_name, last_name, email, registration_date, city)
VALUES ('Test', 'User', 'test@email.com', CURRENT_DATE, 'Test City');

UPDATE customers 
SET total_purchases = 0 
WHERE email = 'test@email.com';

-- If everything looks good:
COMMIT;  -- Make changes permanent

-- Or if something went wrong:
-- ROLLBACK;  -- Undo all changes in this transaction

Real-World Transaction Example

Let's say you're processing a batch of customer updates and want to make sure they all succeed or all fail together:

BEGIN TRANSACTION;

-- Update customer email addresses
UPDATE customers SET email = 'new.sarah@email.com' WHERE customer_id = 101;
UPDATE customers SET email = 'new.mike@email.com' WHERE customer_id = 102;
UPDATE customers SET email = 'new.jennifer@email.com' WHERE customer_id = 103;

-- Check if all updates worked (3 rows should be affected total)
-- If the counts look right, COMMIT
-- If something's wrong, ROLLBACK

COMMIT;

Testing Changes Before Committing

Within a transaction, you can SELECT to verify your changes before committing:

BEGIN TRANSACTION;

UPDATE customers 
SET customer_tier = 'Gold' 
WHERE total_purchases >= 1000;

-- Check what changed
SELECT customer_id, first_name, last_name, customer_tier, total_purchases
FROM customers 
WHERE customer_tier = 'Gold';

-- If this looks right, COMMIT
-- If not, ROLLBACK and try again
COMMIT;

Note: Different databases have slightly different transaction syntax. Some use BEGIN, others use START TRANSACTION. Check your database documentation.

Hands-On Exercise

Let's work through a complete scenario. You're managing a product_inventory table with these columns:

• product_id (integer)
• product_name (text)
• category (text)
• price (decimal)
• quantity_in_stock (integer)
• last_updated (date)

Step 1: Insert New Products

Add three new products to your inventory:

INSERT INTO product_inventory (product_id, product_name, category, price, quantity_in_stock, last_updated)
VALUES 
    (201, 'Wireless Headphones', 'Electronics', 89.99, 50, CURRENT_DATE),
    (202, 'Coffee Mug', 'Kitchen', 12.99, 100, CURRENT_DATE),
    (203, 'Notebook', 'Office', 4.99, 200, CURRENT_DATE);

Step 2: Update Prices

The supplier raised prices on all Electronics by 10%:

BEGIN TRANSACTION;

UPDATE product_inventory
SET price = price * 1.10,
    last_updated = CURRENT_DATE
WHERE category = 'Electronics';

-- Check the changes
SELECT product_id, product_name, price, last_updated
FROM product_inventory
WHERE category = 'Electronics';

COMMIT;

Step 3: Update Stock After Sales

Process some sales (reduce quantities):

UPDATE product_inventory
SET quantity_in_stock = quantity_in_stock - 5,
    last_updated = CURRENT_DATE
WHERE product_id = 201;  -- Sold 5 wireless headphones

UPDATE product_inventory
SET quantity_in_stock = quantity_in_stock - 25,
    last_updated = CURRENT_DATE
WHERE product_id = 202;  -- Sold 25 coffee mugs

Step 4: Remove Discontinued Products

Delete products that are out of stock and haven't been updated in over 6 months:

-- First, check which products would be deleted
SELECT product_id, product_name, quantity_in_stock, last_updated
FROM product_inventory
WHERE quantity_in_stock = 0 
  AND last_updated < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH);

-- If the results look correct, then delete them
DELETE FROM product_inventory
WHERE quantity_in_stock = 0 
  AND last_updated < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH);

Common Mistakes & Troubleshooting

Mistake 1: Forgetting the WHERE Clause

Wrong:

UPDATE customers SET city = 'Unknown';

This sets every customer's city to 'Unknown'!

Right:

UPDATE customers 
SET city = 'Unknown' 
WHERE city IS NULL;

Mistake 2: Using = Instead of IN for Multiple Values

Wrong:

DELETE FROM customers 
WHERE city = 'Seattle' OR city = 'Portland' OR city = 'Tacoma';

Better:

DELETE FROM customers 
WHERE city IN ('Seattle', 'Portland', 'Tacoma');

Mistake 3: Not Testing DELETE/UPDATE First

Always test your WHERE clause with SELECT before running the actual modification:

-- Test first
SELECT COUNT(*) FROM customers WHERE registration_date < '2020-01-01';

-- If the count looks reasonable, then delete
DELETE FROM customers WHERE registration_date < '2020-01-01';

Mistake 4: Column/Value Mismatch in INSERT

Wrong:

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'john@email.com', 'Smith');  -- Wrong order!

Right:

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Smith', 'john@email.com');

Mistake 5: Forgetting Quotes Around Text Values

Wrong:

INSERT INTO customers (first_name, email)
VALUES (Sarah, sarah@email.com);  -- Missing quotes

Right:

INSERT INTO customers (first_name, email)
VALUES ('Sarah', 'sarah@email.com');

Troubleshooting Tips

Problem: "Column 'customer_id' cannot be null" Solution: Either include a value for customer_id or make sure it's set to auto-increment.

Problem: "Duplicate entry for key 'PRIMARY'" Solution: You're trying to insert a row with a primary key that already exists. Use a different ID or let auto-increment handle it.

Problem: UPDATE or DELETE affected 0 rows Solution: Your WHERE clause didn't match any rows. Check your condition with SELECT first.

Problem: Changes disappeared after closing connection Solution: You forgot to COMMIT your transaction, or you ran ROLLBACK accidentally.

Safety Best Practices

1. Always Use Transactions for Multiple Changes

BEGIN TRANSACTION;
-- All your changes here
COMMIT;  -- or ROLLBACK if there's a problem

2. Test with SELECT Before Modifying

-- Test what would be deleted
SELECT COUNT(*) FROM customers WHERE last_purchase_date < '2022-01-01';

-- Only delete if the count looks reasonable
DELETE FROM customers WHERE last_purchase_date < '2022-01-01';

3. Start with LIMIT When Learning

Some databases support LIMIT in UPDATE and DELETE:

-- Delete only 10 rows at a time while testing
DELETE FROM old_logs 
WHERE created_date < '2023-01-01' 
LIMIT 10;

4. Keep Backups

Before major data changes, consider backing up your table:

-- Create a backup table
CREATE TABLE customers_backup AS 
SELECT * FROM customers;

-- Do your changes
UPDATE customers SET city = 'New Value' WHERE condition;

-- If something goes wrong, you can restore from customers_backup

5. Use Specific WHERE Clauses

Avoid broad conditions that might affect more rows than intended:

Risky:

DELETE FROM orders WHERE status = 'pending';

Safer:

DELETE FROM orders 
WHERE status = 'pending' 
  AND created_date < '2023-01-01'
  AND customer_id IS NOT NULL;

Summary & Next Steps

You now have the core skills to modify database data safely and effectively. You can:

• Insert new records one at a time or in batches
• Update existing records with precise WHERE conditions
• Delete unwanted data without destroying everything
• Use transactions to group changes and recover from mistakes
• Apply best practices that prevent data disasters

Key takeaways:

• Always test your WHERE clause with SELECT first
• Use transactions for important changes
• Be specific—avoid broad UPDATE and DELETE statements
• When in doubt, back up your data first

What's next: Now that you can modify data, you're ready to learn about more advanced topics like joining tables, creating views, and working with indexes. These skills will help you work with complex, real-world databases where data is spread across multiple related tables.

Practice these commands on test data until they feel natural. The muscle memory of writing safe, precise data modification statements will serve you throughout your data career.