Modifying Data: INSERT, UPDATE, DELETE

So far we've only read data with SELECT. Now let's learn how to write, change, and delete data.

Warning: Unlike SELECT, these statements permanently change your data. Always double-check your WHERE clauses before running UPDATE or DELETE!

INSERT — Adding New Rows

Basic INSERT

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Add a new user
INSERT INTO users (id, name, email)
VALUES (6, 'Frank Miller', 'frank@example.com');

Insert Multiple Rows

INSERT INTO products (id, name, price, category, stock)
VALUES
  (8, 'Monitor 4K', 599.99, 'Electronics', 30),
  (9, 'Mouse', 49.99, 'Electronics', 150),
  (10, 'Mousepad', 19.99, 'Office', 200);

Insert Without Specifying All Columns

Columns with defaults or that allow NULL can be omitted:

-- 'stock' has a default of 0, 'created_at' has a default
INSERT INTO users (id, name, email)
VALUES (7, 'Grace Lee', 'grace@example.com');

INSERT ... SELECT

Insert rows from another table or query:

-- Hypothetical: copy cheap products to an archive table
INSERT INTO cheap_products (name, price)
SELECT name, price FROM products WHERE price < 50;

UPDATE — Modifying Existing Rows

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

-- Update Bob's email address
UPDATE users
SET email = 'robert.smith@example.com'
WHERE id = 2;

Update Multiple Columns

UPDATE products
SET price = 1199.99, stock = 40
WHERE id = 1;

Update with Calculation

-- Increase all Electronics prices by 10%
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';

The Danger of UPDATE Without WHERE

-- ⚠️ This updates EVERY row in the table!
UPDATE products SET price = 0;

-- ✅ Always include a WHERE clause
UPDATE products SET price = 0 WHERE id = 99;

DELETE — Removing Rows

DELETE FROM table_name
WHERE condition;

-- Delete a specific user
DELETE FROM users WHERE id = 7;

-- Delete all out-of-stock products
DELETE FROM products WHERE stock = 0;

The Danger of DELETE Without WHERE

-- ⚠️ Deletes ALL rows! (The table structure remains)
DELETE FROM users;

-- Use TRUNCATE for fast full-table deletion (irreversible!)
-- TRUNCATE TABLE users;

RETURNING (PostgreSQL / SQLite 3.35+)

Get the affected rows back after INSERT/UPDATE/DELETE: