SQL Writes: INSERT, UPDATE, DELETE

Every query you've written so far has been read-only — you've been asking questions, not changing anything. Real applications also need to add new records, correct mistakes, and remove outdated data.

This is where things get higher-stakes. A SELECT that goes wrong returns bad data. An UPDATE that goes wrong changes data — potentially across thousands of rows, permanently. Let's learn to write mutations carefully.

---

Quick Reference

INSERT:

INSERT INTO products (name, price, category, stock_quantity)
VALUES ('New Product', 49.99, 'Electronics', 100);

UPDATE (always check with SELECT first):

-- Step 1: preview
SELECT id, price FROM products WHERE id = 3;

-- Step 2: update
UPDATE products SET price = 44.99 WHERE id = 3;

DELETE (always check with SELECT first):

-- Step 1: preview
SELECT * FROM products WHERE stock_quantity = 0;

-- Step 2: delete
DELETE FROM products WHERE stock_quantity = 0;

Transaction safety net:

BEGIN;
  UPDATE products SET price = 44.99 WHERE id = 3;
  -- Verify the result looks correct
  SELECT id, price FROM products WHERE id = 3;
ROLLBACK;  -- Undo everything if something looks wrong
-- or COMMIT; to make it permanent

Gotchas:

• ⚠️ UPDATE products SET price = 0 with no WHERE updates every row
• ⚠️ DELETE FROM products with no WHERE deletes every row
• ⚠️ Always SELECT with the same WHERE condition before UPDATE or DELETE
• ⚠️ ROLLBACK only works inside a BEGIN block — once you COMMIT, it's permanent

---

Version Information

Tested with:

• PostgreSQL: 15.x, 16.x
• Node.js: v18.x, v20.x, v22.x

Last verified: May 2025

---

What You Need to Know First

Required reading:

• SQL Basics: Reading Data with SELECT — WHERE clauses are essential for safe writes
• Relational Databases: How Structured Data Works — foreign key constraints affect what you can insert and delete

What We'll Cover in This Article

By the end of this guide, you'll understand:

• How to insert single and multiple rows with INSERT INTO
• How to use RETURNING to get data back from a write
• How to update rows safely with UPDATE ... SET ... WHERE
• Why UPDATE without WHERE is one of the most dangerous commands in SQL
• How to delete rows with DELETE FROM ... WHERE
• How BEGIN, COMMIT, and ROLLBACK create a safety net for destructive operations
• How foreign key constraints protect your data during writes

What We'll Explain Along the Way

We'll introduce these concepts with full explanations:

• DML (Data Manipulation Language) — the category these commands belong to
• What "autocommit" means and why it matters for safety
• The difference between DELETE and TRUNCATE
• What ON CONFLICT does (upserts)

---

Part 1: INSERT — Adding New Rows

INSERT INTO adds one or more rows to a table.

Single Row Insert

-- Add a new product to the ShopFlow catalog
INSERT INTO products (name, description, price, stock_quantity, category)
VALUES ('Standing Desk Mat', 'Anti-fatigue mat, 90x60cm', 79.99, 50, 'Accessories');

The column list and the VALUES list must match in order and count. If you omit the column list, you must provide values for every column in the exact order they were defined in the table — a fragile approach that breaks whenever the schema changes.

-- ❌ Fragile: depends on column order in the table definition
INSERT INTO products
VALUES (9, 'Standing Desk Mat', 'Anti-fatigue mat, 90x60cm', 79.99, 50, 'Accessories');

-- ✅ Explicit: works regardless of column order in the table
INSERT INTO products (name, description, price, stock_quantity, category)
VALUES ('Standing Desk Mat', 'Anti-fatigue mat, 90x60cm', 79.99, 50, 'Accessories');

Notice we didn't include id — the SERIAL type generates it automatically. The database assigns the next available integer.

Getting the Generated ID Back: RETURNING

After an insert, you often need to know the ID that was assigned — for example, to immediately create related records. RETURNING solves this without a separate SELECT:

-- Insert a product and immediately retrieve its assigned id
INSERT INTO products (name, description, price, stock_quantity, category)
VALUES ('Standing Desk Mat', 'Anti-fatigue mat, 90x60cm', 79.99, 50, 'Accessories')
RETURNING id, name;

id	name
9	Standing Desk Mat

RETURNING can return any column from the inserted row. RETURNING * returns all columns. This is a PostgreSQL feature — most other databases require a separate query to retrieve the generated ID.

In a real workflow, you'd use this to chain inserts:

-- Insert a new order and immediately insert its items
INSERT INTO orders (user_id, status)
VALUES (1, 'pending')
RETURNING id;
-- Returns: id = 7

INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase)
VALUES (7, 9, 1, 79.99);

Multiple Row Insert

Insert several rows in one statement — more efficient than one insert per row:

-- Add three users at once
INSERT INTO users (name, email) VALUES
  ('Fatima Al-Zahra', 'fatima@example.com'),
  ('Diego Hernandez', 'diego@example.com'),
  ('Yuki Tanaka',     'yuki@example.com');

RETURNING works here too and returns one row per inserted record:

INSERT INTO users (name, email) VALUES
  ('Fatima Al-Zahra', 'fatima@example.com'),
  ('Diego Hernandez', 'diego@example.com')
RETURNING id, name;

id	name
6	Fatima Al-Zahra
7	Diego Hernandez

ON CONFLICT: Upserts

Sometimes you want to insert a row if it doesn't exist, or update it if it does. This is called an upsert:

-- Insert a user, but if email already exists, update the name instead
INSERT INTO users (name, email)
VALUES ('Aisha Mwangi-Updated', 'aisha@example.com')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

EXCLUDED refers to the row that would have been inserted. This is the standard PostgreSQL upsert pattern — useful for syncing data from external sources where you don't know if a record already exists.

---

Part 2: UPDATE — Modifying Existing Rows

UPDATE changes the values of columns in rows that match a condition.

The Safe UPDATE Pattern

Before running any UPDATE, run the equivalent SELECT first. This confirms your WHERE clause targets exactly the rows you intend:

-- Step 1: See which rows will be affected
SELECT id, name, price FROM products WHERE id = 3;

id	name	price
3	Laptop Stand	49.99

Good — exactly one row, and it's the right one. Now update:

-- Step 2: Run the UPDATE
UPDATE products
SET price = 44.99
WHERE id = 3;

PostgreSQL responds with UPDATE 1 — meaning one row was changed. If you see UPDATE 0, your WHERE condition matched nothing. If you see UPDATE 8, you've just changed all 8 products — probably not what you meant.

Updating Multiple Columns

Set multiple columns in one UPDATE by separating assignments with commas:

-- Update price and stock quantity together
UPDATE products
SET
  price          = 44.99,
  stock_quantity = 60
WHERE id = 3;

Using RETURNING with UPDATE

Just like INSERT, UPDATE supports RETURNING to see what changed:

UPDATE products
SET price = 44.99
WHERE id = 3
RETURNING id, name, price;

id	name	price
3	Laptop Stand	44.99

This is useful for confirming the update applied correctly, or returning the new value to the calling application.

Updating Based on Other Columns

You can reference the current column value on the right side of SET:

-- Apply a 10% discount to all Electronics products
UPDATE products
SET price = price * 0.90
WHERE category = 'Electronics';

And update based on a calculation involving other columns:

-- Reduce stock by the number sold in order_items
-- (for illustration — in practice you'd use a transaction)
UPDATE products
SET stock_quantity = stock_quantity - (
  SELECT SUM(quantity) FROM order_items WHERE product_id = products.id
)
WHERE id IN (SELECT DISTINCT product_id FROM order_items);

⚠️ The Most Dangerous Command: UPDATE Without WHERE

If you omit the WHERE clause, PostgreSQL updates every single row in the table. No warning, no confirmation — it just does it:

-- ❌ DANGER: sets price to 0 for ALL 8 products
UPDATE products SET price = 0;

PostgreSQL responds with UPDATE 8. Every product now has a price of zero. This is not hypothetical — it's one of the most common production database mistakes.

The rule is simple and absolute: always write and verify your WHERE clause before adding SET.

---

Part 3: DELETE — Removing Rows

DELETE FROM removes rows that match a condition.

The Safe DELETE Pattern

Same principle as UPDATE — run SELECT with the same WHERE first:

-- Step 1: See what will be deleted
SELECT id, name, stock_quantity FROM products WHERE stock_quantity = 0;

id	name	stock_quantity
3	Laptop Stand	0
7	Cable Management	0

Two products are out of stock. Confirm this is what you want to delete, then:

-- Step 2: Delete them
DELETE FROM products WHERE stock_quantity = 0;
-- Response: DELETE 2

RETURNING with DELETE

RETURNING works on DELETE too — useful for logging what was removed or returning deleted records to the application:

DELETE FROM products
WHERE stock_quantity = 0
RETURNING id, name;

id	name
3	Laptop Stand
7	Cable Management

Foreign Key Constraints Protect You

If you try to delete a row that other rows depend on via a foreign key, PostgreSQL refuses:

-- ❌ Cannot delete user 1 (Aisha) — she has orders referencing her id
DELETE FROM users WHERE id = 1;

-- ERROR: update or delete on table "users" violates foreign key
-- constraint "orders_user_id_fkey" on table "orders"
-- DETAIL: Key (id)=(1) is still referenced from table "orders".

This is the foreign key constraint enforcing referential integrity. You have two options:

1. Delete the dependent rows first (DELETE FROM orders WHERE user_id = 1), then delete the user
2. Configure the foreign key with ON DELETE CASCADE — which automatically deletes dependent rows when the parent is deleted (see Foreign Keys and Relationships for more details)

⚠️ DELETE Without WHERE

Like UPDATE, DELETE without WHERE affects every row:

-- ❌ DANGER: deletes all 8 products
DELETE FROM products;
-- Response: DELETE 8

Unlike dropping the table, this preserves the table structure — just empties it completely. For intentionally emptying a table, TRUNCATE is faster:

-- Faster than DELETE for emptying entire tables
TRUNCATE TABLE products;

-- TRUNCATE with cascade (also empties tables that reference this one)
TRUNCATE TABLE products CASCADE;

TRUNCATE is not transactional in all databases, but it is in PostgreSQL — it can be rolled back inside a BEGIN block.

---

Part 4: Transactions — Your Safety Net

All the mutations above auto-commit immediately. The moment UPDATE runs, the change is permanent. If you made a mistake, there's no undo.

Transactions wrap multiple statements into a single all-or-nothing unit. Nothing is committed until you explicitly say so — giving you a chance to review or abort.

BEGIN, COMMIT, ROLLBACK

-- Start a transaction
BEGIN;

  -- Make some changes
  UPDATE products SET price = 44.99 WHERE id = 3;

  -- Review the result before committing
  SELECT id, name, price FROM products WHERE id = 3;

-- If everything looks correct:
COMMIT;

-- If something is wrong:
-- ROLLBACK;

Inside a BEGIN block:

• Your changes are visible to your own session — you can SELECT to verify them
• Other sessions don't see your changes until you COMMIT
• ROLLBACK undoes everything back to the BEGIN
• COMMIT makes everything permanent and releases the transaction

A Practical Example: Placing an Order

Here's a realistic transaction — placing an order involves several related inserts that must all succeed or all fail:

BEGIN;

  -- 1. Create the order
  INSERT INTO orders (user_id, status)
  VALUES (2, 'pending')
  RETURNING id;
  -- Returns: id = 7

  -- 2. Add items to the order
  INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase)
  VALUES
    (7, 1, 1, 89.99),  -- Wireless Keyboard
    (7, 6, 1, 59.99);  -- Monitor Light

  -- 3. Reduce stock for each ordered product
  UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 1;
  UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 6;

  -- 4. Verify everything looks correct
  SELECT p.name, p.stock_quantity
  FROM products p
  WHERE p.id IN (1, 6);

COMMIT;

If the server crashes after step 2 but before step 4, PostgreSQL automatically rolls back the entire transaction on restart. Either all four steps complete, or none of them do. Your inventory and order data stay consistent.

What Happens If an Error Occurs Inside a Transaction?

In PostgreSQL, any error inside a transaction automatically aborts it — you must ROLLBACK before you can do anything else:

BEGIN;
  UPDATE products SET price = 44.99 WHERE id = 3;

  -- Oops — this causes an error (violates a constraint)
  INSERT INTO products (name, price, category, stock_quantity)
  VALUES (NULL, 49.99, 'Electronics', 10);  -- name is NOT NULL

-- PostgreSQL now refuses any further statements:
-- ERROR: current transaction is aborted,
-- commands ignored until end of transaction block

  -- You must rollback to reset
ROLLBACK;

This behavior is called error-aborting — it prevents a partial transaction from being committed in a broken state.

---

Common Misconceptions

❌ Misconception: UPDATE/DELETE without WHERE is rare or caught by the database

Reality: PostgreSQL executes UPDATE products SET price = 0 and DELETE FROM products without any warning or confirmation. There is no prompt. There is no "are you sure?" The database assumes you know what you're doing.

Why this matters: This mistake happens to experienced developers too — a momentary lapse in a psql session is all it takes.

Prevention:

-- Before any UPDATE or DELETE, run this SELECT first
SELECT id, name FROM products WHERE stock_quantity = 0;
-- Confirm the result, THEN write the UPDATE/DELETE with the same WHERE
DELETE FROM products WHERE stock_quantity = 0;

❌ Misconception: ROLLBACK works even after COMMIT

Reality: ROLLBACK only undoes changes made since the last BEGIN. Once you COMMIT, the transaction is closed and the changes are permanent. There is no undo for a committed transaction at the SQL level — recovery requires backups or point-in-time restore at the database server level.

Why this matters: Don't rely on ROLLBACK as a general undo. Use it as a deliberate safety mechanism inside a BEGIN block before you've committed.

❌ Misconception: Transactions are only for multi-step operations

Reality: Even a single UPDATE benefits from a transaction when you're running it manually — it lets you verify the result before committing.

-- Safe way to run even a "simple" update manually
BEGIN;
UPDATE products SET price = 44.99 WHERE id = 3;
SELECT id, price FROM products WHERE id = 3;  -- verify
COMMIT;  -- or ROLLBACK if something looks wrong

---

Troubleshooting Common Issues

Problem: UPDATE 0 — no rows were changed

Symptoms: Your UPDATE runs without error but the response is UPDATE 0.

Common causes:

1. Your WHERE condition doesn't match any rows (wrong ID, wrong value)
2. The column value is already what you're setting it to
3. The WHERE uses = NULL instead of IS NULL

Diagnostic steps:

-- Run the equivalent SELECT to see what matches
SELECT * FROM products WHERE id = 99;
-- If this returns 0 rows, the UPDATE would also affect 0 rows

Problem: ERROR: violates foreign key constraint

Symptoms: INSERT or DELETE fails with a foreign key error.

Cause:

• On INSERT: you're referencing a parent row that doesn't exist (orders.user_id = 99 but user 99 doesn't exist)
• On DELETE: you're deleting a row that other rows depend on

Solutions:

-- For INSERT: verify the parent row exists first
SELECT id FROM users WHERE id = 99;

-- For DELETE: delete child rows first
DELETE FROM order_items WHERE order_id = 7;
DELETE FROM orders WHERE id = 7;
-- Now you can delete the user if needed

Problem: ERROR: current transaction is aborted

Symptoms: After an error inside a BEGIN block, every subsequent statement fails with this message.

Cause: PostgreSQL aborts the transaction on error and blocks further statements until you roll back.

Solution:

-- Always run ROLLBACK after a transaction error before doing anything else
ROLLBACK;
-- Now you can start fresh with BEGIN or run individual statements

---

Check Your Understanding

Quick Quiz

1. Why should you always run SELECT before UPDATE or DELETE?

   Show Answer

   The SELECT with the same WHERE clause lets you preview exactly which rows will be affected. This catches mistakes before they're permanent — for example, a missing WHERE that would affect all rows, or a typo in an ID that would affect the wrong row.

2. What's wrong with this approach?

   DELETE FROM order_items;
   DELETE FROM orders;

   Show Answer

   Both statements have no WHERE clause — they delete all rows from both tables. The first deletes every order item, the second deletes every order. The database executes these without any warning.

   The correct approach adds WHERE conditions and ideally wraps everything in a BEGIN/ROLLBACK/COMMIT block.

3. You run BEGIN, then UPDATE products SET price = 0, then realize your mistake. What do you do?

   Show Answer

   Run ROLLBACK. This undoes all changes made since BEGIN — the prices return to their original values. Nothing was committed, so the data is safe.

   If you had already run COMMIT, ROLLBACK would have no effect — the change would be permanent.

Hands-On Challenge

Task: A new product line is launching. Write a transaction that:

1. Inserts a new product: "Ergonomic Chair", category "Furniture", price $299.99, stock 25
2. Immediately creates an order for user 1 containing 1 unit of this new product at its launch price
3. Reduces the product's stock by 1

Wrap everything in a transaction, verify the stock is correct before committing.

Show Solution

BEGIN;

-- Step 1: Insert the new product and capture its id
INSERT INTO products (name, description, price, stock_quantity, category)
VALUES ('Ergonomic Chair', 'Lumbar support, adjustable armrests', 299.99, 25, 'Furniture')
RETURNING id;
-- Assume this returns id = 9

-- Step 2: Create an order for user 1
INSERT INTO orders (user_id, status)
VALUES (1, 'pending')
RETURNING id;
-- Assume this returns id = 7

-- Step 3: Add the product to the order
INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase)
VALUES (7, 9, 1, 299.99);

-- Step 4: Reduce stock
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 9;

-- Step 5: Verify stock is now 24
SELECT id, name, stock_quantity FROM products WHERE id = 9;

-- If stock shows 24 as expected:
COMMIT;
-- If anything looks wrong:
-- ROLLBACK;

Note: In real application code (not manual psql), the IDs returned by RETURNING are captured in variables by your programming language and used in subsequent statements.

---

Summary: Key Takeaways

• INSERT INTO table (columns) VALUES (values) adds rows; always specify the column list explicitly
• RETURNING retrieves data from the affected rows after INSERT, UPDATE, or DELETE — avoiding a separate SELECT
• UPDATE table SET column = value WHERE condition modifies rows; always include WHERE
• DELETE FROM table WHERE condition removes rows; always include WHERE
• UPDATE and DELETE without WHERE affect every row — PostgreSQL gives no warning
• The safe pattern: SELECT with your WHERE first, confirm the target rows, then run UPDATE or DELETE
• BEGIN starts a transaction; COMMIT makes changes permanent; ROLLBACK undoes everything since BEGIN
• Transactions give you a review window for destructive operations — use them for any manual data change
• Foreign key constraints prevent inserting orphaned records and deleting referenced records — this is protection, not obstruction

---

What's Next?

You can now read, filter, aggregate, join, and write data. That's the full SQL toolkit for working with an existing database. But we've been treating the schema as something that already exists — we haven't built it ourselves.

The next step would be to create the ShopFlow schema from scratch using CREATE TABLE, choose the right data types, and add constraints that make the database enforce your business rules automatically.