Changing Data: INSERT, UPDATE, and DELETE
Alright, so far you’ve been safely reading data with SELECT and WHERE. That’s great, but here’s the thing: databases aren’t just libraries where you look things up. They’re living, breathing systems where data gets added, changed, and sometimes removed. Welcome to the world of data modification.
Before we dive in, let me give you the most important piece of advice you’ll ever get about changing data: always think twice, execute once. Unlike SELECT, these commands actually change your data. There’s no “undo” button in most databases.
The Three Data-Changing Commands
SQL gives you three ways to modify data:
- INSERT - Add new rows to a table
- UPDATE - Change existing data in rows
- DELETE - Remove rows from a table
Each one is powerful, and each one can get you into trouble if you’re not careful. Let’s learn to use them safely.
INSERT: Adding New Data
INSERT is how you add new rows to a table. Think of it like filling out a form and submitting it to the database.
Basic INSERT Syntax
1INSERT INTO table_name (column1, column2, column3)
2VALUES (value1, value2, value3);Here’s a real example:
1INSERT INTO employees (first_name, last_name, department, salary, hire_date)
2VALUES ('Sarah', 'Connor', 'IT', 75000, '2024-01-15');This creates a new employee record. The database takes your values and creates a brand new row.
Column Order Matters
The values you provide must match the columns you specify, in the exact same order:
1-- This works:
2INSERT INTO employees (first_name, last_name, salary)
3VALUES ('John', 'Smith', 65000);
4
5-- This would be a disaster:
6INSERT INTO employees (first_name, last_name, salary)
7VALUES (65000, 'John', 'Smith'); -- Wrong! Salary in first_name field!INSERT Multiple Rows at Once
Instead of running multiple INSERT statements, you can add several rows in one command:
1INSERT INTO employees (first_name, last_name, department, salary)
2VALUES
3 ('Mike', 'Johnson', 'Sales', 58000),
4 ('Lisa', 'Williams', 'Marketing', 62000),
5 ('David', 'Brown', 'IT', 70000);This is faster and more efficient than three separate INSERT statements.
Handling Optional Columns
Not every column needs a value. If a column allows NULL values or has a default value, you can skip it:
1-- Skip middle_name (allows NULL) and hire_date (has default of today)
2INSERT INTO employees (first_name, last_name, department, salary)
3VALUES ('Emma', 'Davis', 'Engineering', 80000);UPDATE: Changing Existing Data
UPDATE lets you modify data that’s already in your table. This is where the WHERE clause becomes absolutely critical.
Basic UPDATE Syntax
1UPDATE table_name
2SET column1 = new_value1, column2 = new_value2
3WHERE condition;Here’s a practical example:
1UPDATE employees
2SET salary = 78000
3WHERE first_name = 'Sarah' AND last_name = 'Connor';This finds Sarah Connor and updates her salary to $78,000.
Updating Multiple Columns
You can change several columns at once:
1UPDATE employees
2SET salary = 85000,
3 department = 'Senior IT',
4 last_review = '2024-01-15'
5WHERE employee_id = 123;The Critical Importance of WHERE in UPDATE
Here’s something that will save your career: always use WHERE with UPDATE unless you really, truly want to change every single row.
1-- This is probably NOT what you want:
2UPDATE employees SET salary = 50000;
3
4-- This changes EVERYONE's salary to $50,000. Every. Single. Person.
5
6-- This is probably what you meant:
7UPDATE employees SET salary = 50000 WHERE employee_id = 456;I’ve seen people accidentally give everyone in a company the same salary because they forgot the WHERE clause. Don’t be that person.
DELETE: Removing Data
DELETE removes entire rows from a table. It’s permanent, it’s powerful, and it’s scary if you’re not careful.
Basic DELETE Syntax
1DELETE FROM table_name
2WHERE condition;Remove a specific employee:
1DELETE FROM employees
2WHERE employee_id = 789;Remove all employees from a specific department:
1DELETE FROM employees
2WHERE department = 'Temp Workers';The WHERE Clause Is Your Safety Net
Just like with UPDATE, forgetting WHERE in a DELETE is catastrophic:
1-- This deletes EVERYONE:
2DELETE FROM employees;
3
4-- This deletes one person:
5DELETE FROM employees WHERE employee_id = 123;That first command would empty your entire employees table. I’ve seen this happen. It’s not fun to explain to your boss.
Real-World Examples
Let’s put this all together with some practical scenarios:
Scenario 1: New Hire Process
1-- Add a new employee
2INSERT INTO employees (first_name, last_name, department, salary, hire_date, status)
3VALUES ('Alex', 'Rodriguez', 'Marketing', 55000, '2024-02-01', 'Active');
4
5-- A week later, they negotiate a higher salary
6UPDATE employees
7SET salary = 60000
8WHERE first_name = 'Alex' AND last_name = 'Rodriguez' AND hire_date = '2024-02-01';Scenario 2: Department Restructuring
1-- Move everyone from 'Customer Service' to 'Customer Success'
2UPDATE employees
3SET department = 'Customer Success'
4WHERE department = 'Customer Service';
5
6-- Give everyone in the new department a 5% raise
7UPDATE employees
8SET salary = salary * 1.05
9WHERE department = 'Customer Success';Scenario 3: Cleanup Operation
1-- Remove all terminated employees from over a year ago
2DELETE FROM employees
3WHERE status = 'Terminated'
4 AND termination_date < '2023-01-01';Safety Best Practices
Here are the habits that’ll save you from disasters:
1. Always Test with SELECT First
Before you UPDATE or DELETE, run a SELECT with the same WHERE clause:
1-- First, see what you're about to change:
2SELECT * FROM employees WHERE department = 'Sales';
3
4-- If that looks right, then update:
5UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';2. Use Transactions When Possible
Many databases let you wrap changes in transactions, so you can rollback if something goes wrong:
1BEGIN TRANSACTION;
2
3UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';
4
5-- Check if it looks right:
6SELECT * FROM employees WHERE department = 'IT';
7
8-- If good: COMMIT;
9-- If bad: ROLLBACK;3. Start with the Most Specific WHERE Clause
Begin with unique identifiers like ID numbers, then expand if needed:
1-- Start specific:
2UPDATE employees SET salary = 75000 WHERE employee_id = 123;
3
4-- Not general:
5UPDATE employees SET salary = 75000 WHERE first_name = 'John'; -- Might be multiple Johns!4. Back Up Important Data
Before major changes, especially DELETE operations, make sure you have backups or can recreate the data.
Common Beginner Mistakes
Forgetting WHERE clauses - This is the big one. Always double-check.
Wrong data types - Trying to insert text into a number column, or forgetting quotes around strings.
Violating constraints - Trying to insert duplicate primary keys, or NULL values into required fields.
Case sensitivity - Some databases care about case in string comparisons.
Date format confusion - Different databases expect dates in different formats.
What You Can’t Undo
Here’s the harsh reality: most databases don’t have an “undo” feature. When you DELETE a row, it’s gone. When you UPDATE a value, the old value is overwritten. This isn’t like a word processor where you can hit Ctrl+Z.
Some enterprise databases have advanced recovery features, but as a developer, you should assume that your changes are permanent.
Practice Safely
If you’re learning, here are safe ways to practice:
- Use a practice database - Never practice on real data
- Create test tables - Make copies of real tables for experimentation
- Start small - Practice with a few rows before working with larger datasets
- Use transactions - Wrap your practice in BEGIN/ROLLBACK blocks
What’s Next
You now know how to safely modify data in databases. These three commands - INSERT, UPDATE, and DELETE - along with your SELECT and WHERE skills, give you complete control over database data.
The next step is learning about more advanced topics like JOINs (combining data from multiple tables) and functions (manipulating data during queries). But honestly? You’ve just learned the core of SQL. Everything else builds on these fundamentals.
Remember: with great power comes great responsibility. You can now change data permanently. Use that power wisely, always think before you execute, and when in doubt, SELECT first to see what you’re about to change.
The database trusts you now. Don’t let it down.