Learn to add, modify, and remove database records with confidence. Master INSERT, UPDATE, and DELETE commands with practical examples and safety best practices that prevent data disasters.
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:
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.
Before we dive into specific commands, let's understand what we're working with. SQL provides three core commands for changing data:
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.
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.
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.
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.
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().
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.
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.
You can update multiple columns in one statement:
UPDATE customers
SET last_name = 'Chen-Williams',
email = 'sarah.williams@email.com'
WHERE customer_id = 101;
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'.
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';
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!
The DELETE statement removes entire rows from a table:
DELETE FROM table_name
WHERE condition;
To delete a specific customer:
DELETE FROM customers
WHERE customer_id = 205;
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';
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 customerswith no WHERE clause deletes every customer in your database. Always double-check your WHERE condition!
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.
-- Single value
WHERE customer_id = 101
WHERE city = 'Seattle'
-- Multiple possible values
WHERE city IN ('Seattle', 'Portland', 'San Francisco')
-- 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-%'
-- 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')
-- 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
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 let you group multiple changes together and roll them back if something goes wrong. Think of it as a safety checkpoint system.
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
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;
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.
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)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);
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;
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
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);
Wrong:
UPDATE customers SET city = 'Unknown';
This sets every customer's city to 'Unknown'!
Right:
UPDATE customers
SET city = 'Unknown'
WHERE city IS NULL;
Wrong:
DELETE FROM customers
WHERE city = 'Seattle' OR city = 'Portland' OR city = 'Tacoma';
Better:
DELETE FROM customers
WHERE city IN ('Seattle', 'Portland', 'Tacoma');
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';
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');
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');
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.
BEGIN TRANSACTION;
-- All your changes here
COMMIT; -- or ROLLBACK if there's a problem
-- 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';
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;
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
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;
You now have the core skills to modify database data safely and effectively. You can:
Key takeaways:
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.