SQL Writes & DDL Notebook

2026-06-13 Data Analysis 5 min read

SQL

The other SQL notebooks only read data. This one changes it — inserting rows, updating values, deleting records, and creating or altering tables. These commands are a small part of DA work but a dangerous one: a SELECT mistake wastes time, an UPDATE mistake destroys data.

Two safety habits run through this whole notebook: preview every write with a SELECT first, and wrap multi-step changes in a transaction.

Syntax is MySQL with PostgreSQL notes where they differ — see the quick map in SQL Notebook for the read-side differences. All examples use the parking system tables.

INSERT

Insert One Row

INSERT INTO car_expenses (date, item, amount)
VALUES ('2025-06-15', 'Insurance', 12000);

List the columns explicitly. INSERT INTO car_expenses VALUES (...) without a column list breaks silently when the table gains a column later.

Insert Several Rows

INSERT INTO car_expenses (date, item, amount)
VALUES ('2025-06-15', 'Insurance', 12000),
       ('2025-06-16', 'Toll',        180),
       ('2025-06-17', 'Maintenance', 3500);

One statement with many VALUES rows is much faster than many single INSERTs — the database commits once instead of per row.

INSERT ... SELECT (Copy from a Query)

-- archive last year's records into a history table
INSERT INTO parking_history_archive (parking_id, license_plate, entry_time, exit_time)
SELECT parking_id, license_plate, entry_time, exit_time
FROM parking_history
WHERE entry_time < '2025-01-01';

The SELECT can be anything — joins, filters, aggregates. This is the standard way to build summary tables and archives.

Get the Generated ID Back

-- MySQL: ask after the insert
INSERT INTO car_expenses (date, item, amount) VALUES ('2025-06-15', 'Toll', 180);
SELECT LAST_INSERT_ID();

-- PostgreSQL: get it in the same statement
INSERT INTO car_expenses (date, item, amount)
VALUES ('2025-06-15', 'Toll', 180)
RETURNING id;

PostgreSQL's RETURNING can return any column of the new row (RETURNING id, created_at) — cleaner than a second query, and the pattern most APIs use.

UPDATE

The Safe Pattern: SELECT First

-- 1. preview exactly which rows will change
SELECT parking_id, parking_type
FROM parking_history
WHERE station_code = 'ST001' AND parking_type = 'hourly';

-- 2. same WHERE clause, swapped verb
UPDATE parking_history
SET parking_type = 'monthly'
WHERE station_code = 'ST001' AND parking_type = 'hourly';

Write the SELECT, check the row count, then change SELECT ... to UPDATE ... SET. The WHERE clause is identical, so what you previewed is what you change.

Update Several Columns

UPDATE management_table
SET station_type = 2,
    bd_team      = 'North Team'
WHERE station_code = 'ST001';

Update from Another Table

-- MySQL: UPDATE with JOIN
UPDATE parking_history ph
JOIN management_table mt ON ph.station_code = mt.station_code
SET ph.city = mt.city
WHERE ph.city IS NULL;

-- PostgreSQL: UPDATE ... FROM
UPDATE parking_history ph
SET city = mt.city
FROM management_table mt
WHERE ph.station_code = mt.station_code
  AND ph.city IS NULL;

DELETE

-- preview first — same WHERE, SELECT verb
SELECT COUNT(*) FROM parking_history WHERE entry_time < '2024-01-01';

DELETE FROM parking_history
WHERE entry_time < '2024-01-01';

Delete with a Subquery

-- remove sessions for stations that were closed
DELETE FROM parking_history
WHERE station_code IN (
    SELECT station_code FROM management_table WHERE status = 'closed'
);

DELETE vs TRUNCATE vs DROP

Command	Removes	WHERE	Rollback	Speed
`DELETE FROM t WHERE ...`	Chosen rows	Yes	Yes (in a transaction)	Row by row
`TRUNCATE TABLE t`	Every row	No	PostgreSQL yes, MySQL no	Instant
`DROP TABLE t`	The whole table	No	PostgreSQL yes, MySQL no	Instant

In MySQL, TRUNCATE and DROP commit implicitly — there is no undo. Treat them like deleting a file.

UPSERT (Insert or Update)

When a row might already exist — daily summary rows, settings, sync jobs — write one statement instead of SELECT-then-decide:

-- MySQL: needs a PRIMARY KEY or UNIQUE index on the conflict column(s)
INSERT INTO daily_station_summary (station_code, day, total_revenue)
VALUES ('ST001', '2025-06-15', 45200)
ON DUPLICATE KEY UPDATE total_revenue = VALUES(total_revenue);

-- PostgreSQL
INSERT INTO daily_station_summary (station_code, day, total_revenue)
VALUES ('ST001', '2025-06-15', 45200)
ON CONFLICT (station_code, day)
DO UPDATE SET total_revenue = EXCLUDED.total_revenue;

-- PostgreSQL: insert only if missing, otherwise do nothing
ON CONFLICT (station_code, day) DO NOTHING;

Both need a unique constraint on the conflict columns — the database can only detect a "duplicate" it was told to enforce.

Transactions

A transaction makes several statements succeed or fail as one unit. Use one whenever a change spans more than a single statement.

START TRANSACTION;          -- BEGIN; in PostgreSQL

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;                     -- make it permanent
-- or, if anything looked wrong:
ROLLBACK;                   -- undo everything since START TRANSACTION

A practical DA pattern — rebuild a summary table safely:

START TRANSACTION;
DELETE FROM daily_station_summary WHERE day = '2025-06-15';
INSERT INTO daily_station_summary (station_code, day, total_revenue)
SELECT station_code, DATE(paid_time), SUM(amount_received)
FROM payment_history
WHERE DATE(paid_time) = '2025-06-15'
GROUP BY station_code, DATE(paid_time);
COMMIT;

If the INSERT fails, the ROLLBACK restores the deleted rows — the table is never left half-empty.

Dialect difference that matters: in MySQL, DDL statements (CREATE, ALTER, DROP, TRUNCATE) commit the current transaction implicitly and cannot be rolled back. PostgreSQL can roll back DDL too — one of the reasons migrations feel safer there.

CREATE TABLE

CREATE TABLE charging_records (
    id            INT AUTO_INCREMENT PRIMARY KEY,    -- SERIAL or GENERATED ... AS IDENTITY in PostgreSQL
    charge_date   DATE          NOT NULL,
    provider      VARCHAR(50)   NOT NULL,
    amount        INT           NOT NULL CHECK (amount >= 0),
    kwh           DECIMAL(6,2)  NOT NULL,
    notes         TEXT,
    created_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);

Choosing Column Types

Data	Use	Avoid
Money	`DECIMAL(10,2)` or integer cents	`FLOAT` / `DOUBLE` — binary floats cannot store 0.1 exactly
Short text (names, codes)	`VARCHAR(n)`	Oversized `VARCHAR(255)` everywhere — pick a real limit
Long free text	`TEXT`	—
Whole numbers	`INT`, or `BIGINT` for ids that will grow	`INT` for ids in high-volume tables
Date only	`DATE`	Storing dates as strings
Date + time	`TIMESTAMP` (PostgreSQL: `TIMESTAMPTZ` if timezone matters)	Strings, epoch ints
True/false	`BOOLEAN` (`TINYINT(1)` in MySQL)	`'Y'/'N'` strings

Constraints

CREATE TABLE payment_history (
    payment_id   INT AUTO_INCREMENT PRIMARY KEY,
    parking_id   INT NOT NULL,
    amount       INT NOT NULL CHECK (amount >= 0),       -- reject bad values at the door
    method       VARCHAR(20) NOT NULL DEFAULT 'Cash',
    paid_time    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (parking_id),                                  -- one payment per session
    FOREIGN KEY (parking_id) REFERENCES parking_history(parking_id)
);

Constraint	Guarantees
`PRIMARY KEY`	Unique, not null — the row's identity
`NOT NULL`	The column always has a value
`UNIQUE`	No duplicates (also what UPSERT detects conflicts on)
`DEFAULT`	A value when the INSERT does not provide one
`CHECK`	The value passes a condition
`FOREIGN KEY`	The value exists in the referenced table

Constraints are cheap insurance: a CHECK that rejects negative amounts at insert time saves an entire data-cleaning investigation later.

CREATE TABLE AS (From a Query)

CREATE TABLE station_summary AS
SELECT station_code, COUNT(*) AS total_visits, SUM(amount_received) AS total_revenue
FROM payment_history
GROUP BY station_code;

Quick for one-off snapshot tables. Note: the new table copies column types from the query but no primary key, indexes, defaults, or constraints — add them afterwards if the table will live on.

ALTER TABLE

-- add a column (with a default so existing rows get a value)
ALTER TABLE charging_records ADD COLUMN provider_type VARCHAR(20) DEFAULT 'public';

-- rename a column
ALTER TABLE charging_records RENAME COLUMN notes TO remarks;

-- change a column's type
ALTER TABLE charging_records MODIFY COLUMN provider VARCHAR(100);          -- MySQL
ALTER TABLE charging_records ALTER COLUMN provider TYPE VARCHAR(100);     -- PostgreSQL

-- drop a column (gone for good — check nothing reads it first)
ALTER TABLE charging_records DROP COLUMN remarks;

-- add a constraint or index later
ALTER TABLE charging_records ADD CONSTRAINT chk_kwh CHECK (kwh >= 0);
CREATE INDEX idx_charge_date ON charging_records(charge_date);

On large tables, ALTER can lock the table while it rebuilds — run schema changes in a quiet window, not mid-day. For indexing strategy, see SQL Advanced Notebook.

Common Mistakes

1. UPDATE or DELETE Without WHERE

UPDATE parking_history SET parking_type = 'monthly';   -- every row in the table, instantly

The single most expensive typo in SQL. Defenses: always write the SELECT preview first; run SET sql_safe_updates = 1; in MySQL sessions (blocks UPDATE/DELETE with no key in the WHERE); and do manual writes inside a transaction so ROLLBACK is available.

2. Multi-Step Writes Without a Transaction

A DELETE that succeeds followed by an INSERT that fails leaves the table half-rebuilt — and nothing tells you. Wrap the pair in START TRANSACTION ... COMMIT so failure rolls both back.

3. Assuming TRUNCATE Can Be Rolled Back (MySQL)

In MySQL, TRUNCATE, DROP, and ALTER commit implicitly — even inside a transaction. If you need an undoable "empty the table", use DELETE FROM t; inside a transaction instead.

4. FLOAT for Money

amount FLOAT          -- 0.1 + 0.2 = 0.30000000000000004
amount DECIMAL(10,2)  -- exact

Binary floats cannot represent most decimal fractions exactly. Sums drift by fractions of a cent, and finance will find them. Use DECIMAL, or store integer cents.

5. INSERT Without a Column List

INSERT INTO t VALUES (...) depends on the current column order. The day someone adds a column, every script written this way breaks — or worse, inserts values into the wrong columns without erroring. Always name the columns.

6. CREATE TABLE AS and Lost Constraints

Snapshot tables made with CREATE TABLE AS have no primary key or NOT NULL constraints. Fine for a throwaway analysis; a trap if the table quietly becomes permanent. If it lives more than a week, give it a proper definition.

For one-off schema changes in a real project, keep each change as a small, re-runnable script (CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS) checked into version control — see Git Notebook for the workflow.

← Back to Blog