SQL INSERT, UPDATE, DELETE Explained — Add, Change and Remove Data

Every app you've ever used — Instagram, your bank, Spotify — stores data somewhere. When you post a photo, a row gets added to a database. When you change your username, a row gets updated. When you delete a message, a row gets removed. The three SQL commands that make all of that happen are INSERT, UPDATE, and DELETE. They're not optional extras — they're the heartbeat of every live application on the planet.

Before these commands existed, changing stored data was a nightmare. You'd have to manually edit flat files, rewrite entire records, or run custom scripts that broke constantly. SQL solved this by giving developers three clean, declarative commands that tell the database exactly what you want done — and the database figures out how to do it efficiently.

By the end of this article you'll be able to add new records to a database table, modify existing ones safely, and remove rows you no longer need — all without breaking anything. You'll also know the one catastrophic mistake most beginners make with DELETE (and exactly how to avoid it).

Setting the Scene — The Table We'll Work With All Article

Before we touch INSERT, UPDATE, or DELETE, we need a table to work with. Think of a table like a spreadsheet with fixed column names. Every row is one record — one item of data.

We're going to build a simple employees table for a fictional company. It has four columns: an ID, a name, a department, and a salary. This is realistic enough to make the examples meaningful, and simple enough that you won't get lost.

Here's the SQL to create that table from scratch. Don't worry too much about the CREATE TABLE syntax — that's a separate topic. Focus on the column names and what kind of data each one holds. Every example in this article will use this exact table, so the story builds naturally from here.

-- Create a fresh database table called 'employees'
-- Think of this as drawing the columns on your whiteboard
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,       -- A unique number to identify each person
    full_name     VARCHAR(100) NOT NULL, -- The person's full name (can't be blank)
    department    VARCHAR(50),           -- Which team they work in
    salary        DECIMAL(10, 2)         -- Their salary, e.g. 52000.00
);

-- At this point the table exists but has ZERO rows in it
-- It's like a spreadsheet with headings but no data yet
SELECT * FROM employees;

SQL INSERT — Writing New Rows Into Your Table

INSERT is how data gets born in a database. Every user account, every order, every log entry starts life as an INSERT statement. The syntax is: tell the table which columns you're filling in, then provide the values in the same order.

There are two styles you'll use constantly. The first inserts one row at a time — useful when your app is responding to a single event, like a user signing up. The second inserts multiple rows in one statement — useful when you're loading a batch of records, like seeding test data.

Always name your columns explicitly in the INSERT statement. If you don't, your query will break silently the moment someone adds or reorders a column in the table schema. Naming columns is a small habit that prevents big disasters.

Notice in the code below how each value lines up with its column. The order of values must match the order of columns you declare — the database won't guess. Get one position wrong and you'll store someone's name in the department field.

-- ── STYLE 1: Insert one row at a time ──────────────────────────────
-- Syntax: INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

INSERT INTO employees (employee_id, full_name, department, salary)
VALUES (1, 'Sarah Mitchell', 'Engineering', 72000.00);
-- Sarah is now in the database. One row added.

INSERT INTO employees (employee_id, full_name, department, salary)
VALUES (2, 'James Okafor', 'Marketing', 55000.00);

INSERT INTO employees (employee_id, full_name, department, salary)
VALUES (3, 'Priya Nair', 'Engineering', 68000.00);


-- ── STYLE 2: Insert multiple rows in ONE statement ──────────────────
-- More efficient for bulk inserts — one trip to the database instead of three

INSERT INTO employees (employee_id, full_name, department, salary)
VALUES
    (4, 'Carlos Mendes',  'HR',          48000.00),  -- Row 1 of batch
    (5, 'Lisa Thornton',  'Engineering', 76000.00),  -- Row 2 of batch
    (6, 'Derek Huang',    'Marketing',   59000.00);  -- Row 3 of batch


-- Check all rows were added successfully
SELECT * FROM employees;

SQL UPDATE — Changing Data That Already Exists

UPDATE is for fixing or changing data that's already sitting in your table. A user changed their email address. An employee got promoted to a new department. A price changed. All of those are UPDATE operations.

The anatomy of UPDATE has three parts: the table name, the SET clause (what you're changing), and the WHERE clause (which rows to change). The WHERE clause is not optional in practice — leave it off and you'll update every single row in the table at once. That's the kind of mistake that ends careers on a Friday afternoon.

You can update multiple columns in one statement by separating them with commas in the SET clause. You don't need to run a separate UPDATE for each column — that would be slow and wasteful.

After every UPDATE in production code, it's good practice to immediately run a SELECT with the same WHERE condition to confirm the change landed correctly. Make it a habit.

-- ── UPDATE 1: Change a single column for one specific row ──────────
-- Sarah got a raise! Update her salary.
-- The WHERE clause targets ONLY the row where employee_id = 1

UPDATE employees
SET salary = 80000.00          -- New value for the salary column
WHERE employee_id = 1;         -- Target only Sarah's row (id = 1)

-- Confirm the change
SELECT full_name, salary FROM employees WHERE employee_id = 1;
-- Output: Sarah Mitchell | 80000.00  ✓


-- ── UPDATE 2: Change multiple columns in one statement ──────────────
-- Carlos moved from HR to Engineering AND got a pay bump

UPDATE employees
SET department = 'Engineering', -- Change department
    salary     = 62000.00       -- Change salary at the same time
WHERE employee_id = 4;          -- Target only Carlos's row

-- Confirm the change
SELECT full_name, department, salary FROM employees WHERE employee_id = 4;
-- Output: Carlos Mendes | Engineering | 62000.00  ✓


-- ── UPDATE 3: Update multiple ROWS at once using a condition ────────
-- Give everyone in Marketing a 10% raise
-- WHERE matches TWO rows: James Okafor and Derek Huang

UPDATE employees
SET salary = salary * 1.10     -- Multiply current salary by 1.10 (add 10%)
WHERE department = 'Marketing'; -- Affects ALL rows where department is Marketing

-- Check both Marketing employees updated
SELECT full_name, department, salary
FROM employees
WHERE department = 'Marketing';

SQL DELETE — Removing Rows You No Longer Need

DELETE removes entire rows from a table. Not a single column — the whole row. If a user deletes their account, their row is gone. If an order is cancelled and you want to purge it, DELETE is the command.

Just like UPDATE, DELETE has a WHERE clause that controls which rows get removed. And just like UPDATE, forgetting WHERE is catastrophic — it deletes everything in the table, leaving an empty shell. The table itself survives, but every single row inside it is gone.

A smart safety habit before running any DELETE is to run a SELECT with the exact same WHERE clause first. If the SELECT returns the rows you expect, you know the DELETE will only touch those rows. Then swap SELECT * for DELETE and run it.

For situations where you want to wipe all rows but keep the table structure, there's a faster command called TRUNCATE — but that's a topic for another day. For targeted, controlled row removal, DELETE with WHERE is your tool.

-- ── SAFETY CHECK FIRST: Run SELECT to see what you're about to delete ──
-- Before deleting, ALWAYS confirm you're targeting the right rows

SELECT * FROM employees WHERE employee_id = 2;
-- Output: 2 | James Okafor | Marketing | 60500.00
-- Good — that's the row we want to remove. Now delete it.


-- ── DELETE 1: Remove one specific row ───────────────────────────────
-- James left the company. Remove his record.

DELETE FROM employees
WHERE employee_id = 2;  -- Targets ONLY the row with id = 2

-- Confirm he's gone
SELECT * FROM employees WHERE employee_id = 2;
-- Output: (0 rows) — the row no longer exists


-- ── DELETE 2: Remove multiple rows matching a condition ──────────────
-- The company is closing the HR department entirely
-- (Note: after UPDATE 2 above, Carlos moved to Engineering, so HR is now empty.
--  Let's add a temp row first to demonstrate)

INSERT INTO employees (employee_id, full_name, department, salary)
VALUES (7, 'Angela Foster', 'HR', 47000.00),
       (8, 'Ben Stafford',  'HR', 44000.00);

-- Now delete everyone in HR in one statement
DELETE FROM employees
WHERE department = 'HR';  -- Deletes Angela and Ben in one go


-- ── See the final state of the table after all operations ───────────
SELECT * FROM employees ORDER BY employee_id;

Frequently Asked Questions