Mohamed Idris

Posted on May 29

Part 07: Writing Data with INSERT, UPDATE, DELETE

#sql #database #learning

Part of the "SQL: Zero to Ninja" series, for junior web devs who want SQL to finally click.

So far you have been a reader. You asked the database questions with SELECT, and no matter how messy your query was, the data stayed safe. The worst that happened was a wrong answer. Now we pick up the pen. We are going to add rows, change rows, and remove rows. This is where SQL gets powerful and a little scary at the same time.

The idea in one line

INSERT adds new rows, UPDATE changes rows that already exist, and DELETE removes rows. The two words that keep you safe are WHERE and "slow down".

The metaphor: editing in pen, not pencil

Think of your database table as a notebook you write in with a pen, not a pencil. There is no soft eraser. Reading (SELECT) is like just looking at the page. But UPDATE and DELETE actually scribble on it.

And here is the scary part. An UPDATE or DELETE with no WHERE is like taking your pen and scribbling over every single page at once. One line of SQL, and the whole notebook is changed forever.

Keep that picture in your head. We will come back to it.

INSERT: adding a new page

You add a row by saying which columns you are filling and what goes in them.

INSERT INTO users (name, email, country)
VALUES ('Sara', 'sara@example.com', 'EG');

You do not have to list id or created_at if the table fills those in for you (more on that in Part 08). You only name the columns you care about.

Insert many rows at once

You do not need one statement per row. Stack them up, separated by commas:

INSERT INTO users (name, email, country)
VALUES
  ('Omar', 'omar@example.com', 'EG'),
  ('Lina', 'lina@example.com', 'SA'),
  ('Noah', 'noah@example.com', 'US');

One trip to the database, three new users. Faster and cleaner than three separate inserts.

Getting the new id back (Postgres)

Here is a problem you will hit in real web apps. A user signs up, you INSERT them, and now you want their new id so you can log them in or create their first order. How do you find out the id the database just made?

In Postgres, you ask for it with RETURNING:

INSERT INTO users (name, email, country)
VALUES ('Sara', 'sara@example.com', 'EG')
RETURNING id;

The database hands you back the fresh id right away. No second query needed. This is one of those small things you will use constantly once you know it exists.

UPDATE: changing a page

UPDATE picks rows and changes some of their columns. It looks like this:

UPDATE users
SET country = 'EG'
WHERE id = 5;

Read it in plain English: "find the user whose id is 5, and set their country to EG." That WHERE id = 5 is the most important line in the whole query.

The horror story

Now look at the exact same query with one line missing:

-- DO NOT run this for real
UPDATE users
SET country = 'EG';

No WHERE. So this does not change one user. It changes every user in the table to country EG. Your one customer in the US, your three in Saudi Arabia, all of them, gone, now all EG. This is the pen scribbling over every page.

There is no Ctrl+Z. The data is just... different now. People have lost their jobs over a missing WHERE. We will fix this habit in a minute.

DELETE: tearing out a page

DELETE removes whole rows.

DELETE FROM orders
WHERE id = 42;

That deletes one order. Fine. But you already see where this is going:

-- DO NOT run this for real
DELETE FROM orders;

No WHERE, so this empties the entire orders table. Every order, gone. Same horror, different verb.

The one habit that saves you

Before you run any UPDATE or DELETE, run a SELECT with the exact same WHERE first. Look at what comes back. Those rows are the ones you are about to change.

-- Step 1: look before you leap
SELECT * FROM users WHERE country = 'EG' AND created_at < '2020-01-01';

-- Step 2: same WHERE, now do the change
UPDATE users
SET status = 'inactive'
WHERE country = 'EG' AND created_at < '2020-01-01';

If Step 1 returns 3 rows, you know Step 2 will touch 3 rows. If Step 1 returns 90,000 rows, stop and think. This tiny habit catches almost every disaster before it happens.

There is also a bigger safety net coming. In Part 12 you will meet transactions, where you can run a change, look at the result, and type ROLLBACK to undo it if it looks wrong. Like a pencil after all, for a short moment. For now, the SELECT-first habit is your seatbelt.

Bonus: create or update in one go (UPSERT)

Sometimes you want "insert this row, but if it already exists, just update it." That is an UPSERT. In Postgres:

INSERT INTO users (email, name)
VALUES ('sara@example.com', 'Sara')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

If the email is new, it inserts. If that email already exists, it updates the name instead of crashing. Handy for "save my profile" buttons. You do not need to master this today, just know the phrase ON CONFLICT exists.

Gotchas juniors hit

Forgetting WHERE. The big one. No WHERE on UPDATE or DELETE means "all rows". Always ask: "did I scope this?"
Running before looking. Skipping the SELECT-first habit because you are "sure". You are not sure. Look anyway.
Confusing UPDATE and INSERT. UPDATE changes rows that exist. INSERT makes new ones. If you UPDATE a user that is not there, nothing happens (zero rows changed), and you might not notice.

Recap

INSERT INTO table (cols) VALUES (...) adds rows. You can add many at once.
RETURNING id (Postgres) hands you the new id right after an insert.
UPDATE ... SET ... WHERE ... changes rows. DELETE FROM ... WHERE ... removes them.
A missing WHERE hits every row. That is the pen scribbling over every page.
Always run a SELECT with the same WHERE first. Transactions (Part 12) let you ROLLBACK.

Your turn

Picture the orders table. You want to delete every order with status 'cancelled'. Before you write the DELETE, write the SELECT you would run first to see what you are about to remove. If you can explain why that SELECT keeps you safe, you have got the most important habit in this whole post.

Next up, Part 08: Designing Tables, Data Types and Constraints, where you learn how those tables get built and how the database can stop bad data before it ever gets in.

Top comments (1)

Mohamed Idris • May 29

Part 07 Practice: INSERT, UPDATE, DELETE

Time to pick up the pen yourself. These use our shared schema: users, orders, products, order_items. Try each one before peeking at the solution. Remember the golden habit: for any UPDATE or DELETE, imagine the matching SELECT first.

1. Add one new user

Add a user named 'Maya' with email 'maya@example.com' from country 'US'.

Solution

INSERT INTO users (name, email, country)
VALUES ('Maya', 'maya@example.com', 'US');

Why: you only name the columns you are filling. id and created_at get filled in by the table for you.

2. Add three products at once

In one statement, add these products: 'Pen' at 2.50 in 'stationery', 'Notebook' at 5.00 in 'stationery', and 'Mug' at 8.00 in 'kitchen'.

Solution

INSERT INTO products (name, price, category)
VALUES
  ('Pen', 2.50, 'stationery'),
  ('Notebook', 5.00, 'stationery'),
  ('Mug', 8.00, 'kitchen');

Why: stacking value rows with commas means one trip to the database instead of three. Faster and cleaner.

3. Insert and get the new id back (Postgres)

Create a new order for user 7 with total 0 and status 'pending', and ask the database to hand you the new order id.

Solution

INSERT INTO orders (user_id, total, status)
VALUES (7, 0, 'pending')
RETURNING id;

Why: in a web app you almost always need that fresh id right after creating a record, for example to add items to this exact order. RETURNING saves you a second query.

4. A careful update (look before you leap)

You want to mark order 42 as 'shipped'. Write the safety SELECT first, then the UPDATE.

Solution

-- Step 1: see exactly what you will change
SELECT * FROM orders WHERE id = 42;

-- Step 2: same WHERE, now change it
UPDATE orders
SET status = 'shipped'
WHERE id = 42;

Why: the SELECT confirms you are touching one row, not the whole table. Same WHERE in both steps is the whole trick.

5. A safe delete pattern

Delete every order with status 'cancelled'. Show the safe two-step pattern.

Solution

-- Step 1: look at what is about to disappear
SELECT * FROM orders WHERE status = 'cancelled';

-- Step 2: same WHERE, now delete
DELETE FROM orders
WHERE status = 'cancelled';

Why: if Step 1 returns way more rows than you expected, you stop before deleting. There is no undo button on a DELETE (until you learn transactions in Part 12).

6. Create or update in one go (UPSERT, Postgres)

A user is saving their profile. Insert 'sara@example.com' with name 'Sara Ali', but if that email already exists, update the name instead of crashing.

Solution

INSERT INTO users (email, name)
VALUES ('sara@example.com', 'Sara Ali')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

Why: ON CONFLICT (email) catches the duplicate and turns it into an update. EXCLUDED.name is the value you tried to insert. Great for "save" buttons that should work whether the record is new or not.

You just learned the three verbs that change data, plus the habit that keeps you out of trouble. Always picture the SELECT first, and you will write data with confidence. See you in Part 08.