✍️ CRUD Operations in SQL

What CRUD Means

CRUD is the four basic operations any persistent store supports. It's such a universal shape that REST APIs, admin panels, and ORMs are all organized around it. In SQL the mapping is direct:

💡 Analogy — the librarian's day: A table is a shelf; CRUD is the librarian's routine. Create shelves a new book, Read looks one up, Update corrects its catalog entry, and Delete removes a damaged copy. Each has a fixed, careful procedure — and the destructive ones (update, delete) are done deliberately, never in bulk by accident.

You already learned Read in the previous lesson. Here we focus on the three that change data, because writes carry risk that reads never do.

Create: INSERT

INSERT adds new rows. Always list the columns explicitly — it documents intent and survives future schema changes.

-- Named columns (recommended)
INSERT INTO customers (customer_name, contact_name, country)
VALUES ('Acme Inc.', 'John Smith', 'USA');

You can omit the column list and supply a value for every column in table order, but that breaks silently the moment someone adds or reorders a column, so avoid it in real code.

Insert many rows at once

INSERT INTO products (product_name, unit_price, category_id) VALUES
    ('Organic Green Tea',   18.50, 1),
    ('Dark Chocolate Bar',   3.95, 2),
    ('Himalayan Pink Salt',  6.75, 2);

A single multi-row insert is far faster than many separate statements, because the database commits them together.

Insert from a query

-- Copy every US customer into an archive table
INSERT INTO customers_usa (customer_id, customer_name, contact_name)
SELECT customer_id, customer_name, contact_name
FROM customers
WHERE country = 'USA';

INSERT INTO users (username, email)
VALUES ('johndoe', 'john@example.com')
RETURNING user_id;

Upsert: insert or update

A very common need is "insert this row, but if it already exists, update it instead." Modern SQL handles this in one statement:

-- PostgreSQL / SQLite
INSERT INTO settings (user_id, theme)
VALUES (42, 'dark')
ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme;

-- MySQL
INSERT INTO settings (user_id, theme)
VALUES (42, 'dark')
ON DUPLICATE KEY UPDATE theme = VALUES(theme);

Read: SELECT (recap)

The previous lesson covered SELECT in depth. As a quick reminder, reading is the safe operation — it never changes data — so it's your best friend for previewing what a risky write will touch:

SELECT product_id, product_name, unit_price, units_in_stock
FROM products
WHERE category_id = 1
ORDER BY unit_price DESC;

Keep this pattern in mind: before every UPDATE or DELETE, run the same WHERE as a SELECT first.

Update: UPDATE

UPDATE changes values in existing rows. The SET clause says what to change; the WHERE clause says which rows.

-- One column, one row
UPDATE products
SET unit_price = 24.99
WHERE product_id = 15;

-- Several columns at once
UPDATE customers
SET contact_name = 'Maria Anders',
    phone        = '030-0074321',
    updated_at   = CURRENT_TIMESTAMP
WHERE customer_id = 4;

Update using the current value

The right-hand side can reference the existing column, which is how you increment, discount, or restock:

-- Give every Beverages product a 10% price rise
UPDATE products
SET unit_price = unit_price * 1.10
WHERE category_id = 1;

-- Deduct sold units and stamp the change
UPDATE products
SET units_in_stock = units_in_stock - 5,
    updated_at     = CURRENT_DATE
WHERE product_id = 10;

Update driven by another table

To set a value based on aggregated data from elsewhere, use a subquery. PostgreSQL and MySQL differ in syntax:

-- PostgreSQL: UPDATE ... FROM
UPDATE customers c
SET discount_rate = CASE
        WHEN o.order_count > 10 THEN 0.15
        WHEN o.order_count >  5 THEN 0.10
        ELSE 0.05
    END
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders GROUP BY customer_id
) o
WHERE c.customer_id = o.customer_id;

-- MySQL: UPDATE ... JOIN
UPDATE customers c
JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders GROUP BY customer_id
) o ON c.customer_id = o.customer_id
SET c.discount_rate = CASE
        WHEN o.order_count > 10 THEN 0.15
        WHEN o.order_count >  5 THEN 0.10
        ELSE 0.05
    END;

-- DANGER: sets EVERY product to 9.99
UPDATE products SET unit_price = 9.99;

Delete: DELETE & TRUNCATE

DELETE removes rows that match a condition.

-- Remove one specific customer
DELETE FROM customers WHERE customer_id = 45;

-- Remove all discontinued, out-of-stock products
DELETE FROM products
WHERE discontinued = TRUE AND units_in_stock = 0;

-- Delete driven by a subquery
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers
    WHERE last_order_date < CURRENT_DATE - INTERVAL '2 years'
);

-- DANGER: deletes EVERY customer
DELETE FROM customers;

TRUNCATE — empty a table fast

When you truly want to remove all rows, TRUNCATE is far faster than a bare DELETE because it deallocates the data instead of logging each row.

TRUNCATE TABLE temp_logs;

💡 Rule of thumb: use DELETE for targeted removals, TRUNCATE only to blank a whole table you're sure about (like a staging or cache table).

The Safety Habit

Destructive statements have no undo button once committed. Build these three reflexes now and they'll save you for the rest of your career.

1. SELECT before you strike. Run your exact WHERE as a SELECT to see which rows will be affected.
2. Wrap it in a transaction. Then you can inspect the row count and roll back if it looks wrong.
3. Check the affected-row count. If you expected to change 1 row and it says 4,000, stop.

BEGIN;                                  -- start a transaction

-- 1. Preview
SELECT * FROM products WHERE product_id = 15;

-- 2. Make the change
UPDATE products SET unit_price = 24.99 WHERE product_id = 15;

-- 3. Verify, then decide
--   Looks right?  COMMIT;
--   Looks wrong?  ROLLBACK;
COMMIT;

Worked Example: Product Lifecycle

Here is one product moving through all four CRUD operations, exactly as an e-commerce backend would drive it.

-- CREATE: a new product arrives
INSERT INTO products (product_name, supplier_id, category_id, unit_price, units_in_stock)
VALUES ('Organic Quinoa', 5, 7, 12.99, 25)
RETURNING product_id;                    -- say it returns 78

-- READ: confirm it landed
SELECT product_id, product_name, unit_price, units_in_stock
FROM products
WHERE product_id = 78;

-- UPDATE: price change + restock after a shipment
UPDATE products
SET unit_price     = 14.99,
    units_in_stock = units_in_stock + 50
WHERE product_id = 78;

-- DELETE: the product is discontinued
DELETE FROM products
WHERE product_id = 78;

The same round trip expressed as the conversation between an app and its database:

Hands-on Exercise

CREATE TABLE inventory (
    item_id    INTEGER PRIMARY KEY,
    name       VARCHAR(80) NOT NULL,
    price      DECIMAL(6,2) NOT NULL,
    qty        INTEGER NOT NULL,
    status     VARCHAR(20) DEFAULT 'active'
);

BEGIN;

-- 1. Create
INSERT INTO inventory (item_id, name, price, qty) VALUES
    (1, 'USB-C Cable',   8.50, 40),
    (2, 'Mechanical Keyboard', 79.00, 0),
    (3, 'Laptop Stand', 34.99, 12);

-- 2. Read
SELECT name, price FROM inventory
WHERE price > 10 ORDER BY price ASC;

-- 3. Update using current value
UPDATE inventory SET price = price * 1.15 WHERE item_id = 3;

-- 4. Bulk status update
UPDATE inventory SET status = 'sold_out' WHERE qty = 0;

-- 5. Preview, then delete
SELECT * FROM inventory WHERE item_id = 1;   -- confirm the target
DELETE FROM inventory WHERE item_id = 1;

ROLLBACK;   -- undo everything (use COMMIT to keep it)

Best Practices

Summary & Quiz

📚 Further Reading

• PostgreSQL — Data Manipulation
• MySQL — INSERT Statement
• SQLite — INSERT / UPDATE / DELETE

🚀 What's Next?

You can now change data in a single table. Real applications spread data across many tables — so next you'll learn Joins and Relationships, the SQL feature that stitches those tables back together.