Chapter 7: Data Manipulation

INSERT, UPDATE, DELETE, and Transactions

7.1 INSERT Basics

-- Insert a single row (named columns)
INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Alice', 'Chen', 'alice@example.com', 'New York');

-- Insert multiple rows
INSERT INTO customers (first_name, last_name, email, city)
VALUES
    ('Bob',   'Smith',  'bob@example.com',   'Chicago'),
    ('Carol', 'Jones',  'carol@example.com', 'Austin'),
    ('David', 'Lee',    NULL,                  'Seattle');

-- INSERT INTO ... SELECT (copy data from one table to another)
INSERT INTO archived_orders (order_id, customer_id, order_date, total)
SELECT order_id, customer_id, order_date, total
FROM orders
WHERE order_date < '2023-01-01';

7.2 UPDATE Statements

-- Update a single column
UPDATE products
SET unit_price = 34.99
WHERE product_id = 1;

-- Update multiple columns
UPDATE products
SET
    unit_price = unit_price * 1.10,
    category   = 'Premium Hardware'
WHERE category = 'Hardware' AND unit_price > 40;

-- UPDATE with a subquery
UPDATE products
SET unit_price = (
    SELECT AVG(unit_price) FROM products
)
WHERE unit_price IS NULL;

7.3 DELETE Statements

-- Delete specific rows
DELETE FROM orders
WHERE order_date < '2020-01-01';

-- Delete all rows (keeps table structure)
DELETE FROM temp_data;

-- Delete rows based on a subquery
DELETE FROM order_items
WHERE order_id IN (
    SELECT order_id FROM orders
    WHERE status = 'cancelled'
);

7.4 UPSERT (INSERT or UPDATE)

-- SQLite: INSERT OR REPLACE
INSERT OR REPLACE INTO products (product_id, product_name, unit_price)
VALUES (1, 'Widget A v2', 32.99);

-- SQLite 3.24+: ON CONFLICT (more flexible)
INSERT INTO products (product_id, product_name, unit_price, in_stock)
VALUES (1, 'Widget A', 32.99, 200)
ON CONFLICT(product_id) DO UPDATE SET
    unit_price = excluded.unit_price,
    in_stock   = excluded.in_stock;

-- PostgreSQL: ON CONFLICT
-- INSERT INTO products (...) VALUES (...)
-- ON CONFLICT (product_id) DO UPDATE SET
--     unit_price = EXCLUDED.unit_price;

7.5 Transactions

A transaction groups multiple statements into an atomic unit: either all succeed or all fail. This prevents partial updates that leave data in an inconsistent state.

-- Transfer inventory between warehouses
BEGIN TRANSACTION;

UPDATE inventory
SET quantity = quantity - 50
WHERE warehouse_id = 1 AND product_id = 101;

UPDATE inventory
SET quantity = quantity + 50
WHERE warehouse_id = 2 AND product_id = 101;

-- If both succeed:
COMMIT;

-- If something goes wrong:
-- ROLLBACK;

7.6 Bulk Data Loading

-- SQLite: import a CSV file
.mode csv
.import sales_data.csv sales

-- PostgreSQL: COPY (fastest for large datasets)
-- COPY sales FROM '/path/to/sales_data.csv'
--     WITH (FORMAT csv, HEADER true);

-- PostgreSQL: \copy (runs client-side, no superuser needed)
-- \copy sales FROM 'sales_data.csv' WITH (FORMAT csv, HEADER true);

7.7 Data Validation

-- Check constraints prevent invalid data at insert/update time
CREATE TABLE products_v2 (
    product_id   INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    unit_price   REAL CHECK(unit_price > 0),
    in_stock     INTEGER DEFAULT 0 CHECK(in_stock >= 0),
    category     TEXT CHECK(category IN ('Electronics', 'Hardware', 'Accessories'))
);

-- This will fail due to CHECK constraint
INSERT INTO products_v2 (product_name, unit_price)
VALUES ('Bad Product', -5.00);
-- Error: CHECK constraint failed