SQL INSERT, UPDATE, DELETE, CREATE TABLE: change data safely

The idea

Until now every query read data. INSERT, UPDATE, DELETE and CREATE TABLE change it. They are short statements, and they act at once: an UPDATE changes every row its WHERE matches, a DELETE removes them, and there is no "are you sure?" in between.

So the skill is not the syntax. It is the habit around it: change a copy rather than the original, look at which rows will be touched before you touch them, and check afterwards that the table says what you think.

Figure 1

UPDATE changes exactly the rows its WHERE matches

staging_products
product_id	category	unit_price
156	Kitchen	53.99
183	Decor	189.00
216	Outdoor	379.00

After UPDATE … SET unit_price = ROUND(unit_price * 1.10, 2) WHERE category = 'Kitchen'
product_id	category	unit_price
156	Kitchen	59.39
183	Decor	189.00
216	Outdoor	379.00

The same UPDATE with the WHERE forgotten
product_id	category	unit_price
156	Kitchen	59.39
183	Decor	207.90
216	Outdoor	416.90

Step 1 of 4 A staging copy of three products. The plan: raise Kitchen prices by 10%.

A staging copy of three products. The plan: raise Kitchen prices by 10%.
First run the WHERE as a SELECT. It returns one row, the knife block. That is the number of rows you expect the UPDATE to change.
Now the UPDATE. Only the matching row changes: 53.99 becomes 59.39. The other rows are not touched.
Without the WHERE, every row matches. Decor and Outdoor rise too, and there is no undo outside a transaction. This is why the preview comes first.

Why it exists

Analysts build tables more often than they expect. A staging copy of last year's orders to clean before a report. A summary table a dashboard reads every morning. An import table for a supplier's price list, which arrives as a file and has to become rows. Each one needs a table to exist (CREATE TABLE), data in it (INSERT, or CREATE TABLE AS SELECT), corrections (UPDATE, DELETE) and sometimes a new column (ALTER TABLE).

The other reason is risk. Reading data cannot hurt anything. Writing data can, and in a real database a mistaken UPDATE on the live table affects whoever reads it next. Learning the safe pattern in a practice environment is what lets you use it without fear at work.

How it works under the hood

Two families of statement

DDL (data definition) changes the structure: CREATE TABLE, ALTER TABLE, DROP TABLE. When you create a table you choose each column's name and type: INTEGER, DECIMAL(10,2), VARCHAR, DATE. The type is a promise about what the column may hold, and the database enforces it, which is why loading '$1,299.00' into a decimal column fails.

DML (data manipulation) changes the rows: INSERT, UPDATE, DELETE. Each reports how many rows it touched. That number is the best check you have.

INSERT and CREATE TABLE AS

INSERT INTO t (a, b) VALUES (…) adds rows you type, and INSERT INTO t SELECT … adds the rows a query returns. CREATE TABLE t AS SELECT … does both in one step: it makes a table shaped like the query's result and fills it. This is the usual way to snapshot data. The new table gets column types from the query, so check them, and note that it does not copy keys or defaults from the source.

UPDATE and DELETE act on matches

Both take a WHERE. Rows that match are changed or removed, and rows that don't are left alone. With no WHERE, everything matches.

Hence the preview habit: write the WHERE as a SELECT first.

Figure 2

DELETE, TRUNCATE and DROP remove different things

staging_lines
order_item_id	quantity
139	2
1210	-4
1581	0
3634	-1

DELETE … WHERE quantity <= 0
order_item_id	quantity
139	2

TRUNCATE staging_lines (table kept, rows gone)
order_item_id	quantity

Step 1 of 4 A staging table with three bad lines: quantities -4, 0 and -1. Across the whole store data there are 24 lines like this.

A staging table with three bad lines: quantities -4, 0 and -1. Across the whole store data there are 24 lines like this.
DELETE … WHERE quantity <= 0 removes the rows that match and keeps the others. One good line remains.
TRUNCATE removes all rows at once and keeps the table with its columns and types, ready to be filled again.
DROP TABLE removes the table itself, definition and rows. Nothing is left to query, which is what you want for a scratch table and a disaster for the wrong one.

DELETE removes some or all rows and can be undone inside a transaction. TRUNCATE empties the whole table quickly and keeps its structure. DROP removes the table. Choose by what you want left over.

ALTER TABLE

ALTER TABLE t ADD COLUMN c DECIMAL(10,2) adds a column. Existing rows get NULL in it until you fill it with an UPDATE, so a typical sequence is add the column, populate it from a calculation, then check there are no NULLs left.

Types and constraints protect you

When you create a table, the column types are your first line of defence, and constraints are the second. NOT NULL refuses a row with no value, PRIMARY KEY refuses a repeated id, and CHECK (quantity > 0) would have refused the 24 bad order lines before they ever got in. A statement that breaks a rule fails without changing anything, which is far better than loading it and cleaning up afterwards. Choose DECIMAL(10,2) for money rather than a floating-point type, so cents do not drift, and DATE or TIMESTAMP for dates rather than text, so you can sort and subtract them.

Reading the row count

Every INSERT, UPDATE and DELETE returns the number of rows it affected. Treat it as a test result. If you expected the 24 bad lines and the database says 2,400, stop and investigate. If it says 0, your WHERE matched nothing, often because of case or a stray space. The count is free and it is the single most useful thing a data change tells you.

Transactions

A transaction groups statements so they take effect together or not at all: COMMIT keeps them, ROLLBACK undoes them. In this course every run is wrapped in a transaction that is rolled back, so the store tables are always restored. On a real database, many tools commit automatically, so do not assume you can roll back.

The workflow

Figure 3

From question to checked answer

CREATE TABLE staging AS SELECT … work on a copy
SELECT … WHERE … see the rows first
UPDATE / DELETE … change the copy
SELECT COUNT(*), SUM(…) check against before
CREATE TABLE report AS … the table people read

Step 1 of 5 Copy the data you need into a staging table. The original stays untouched, so a mistake costs you the copy, not the source.

Copy the data you need into a staging table. The original stays untouched, so a mistake costs you the copy, not the source.
Write the WHERE as a SELECT and look at the rows and the count. If the count is not the one you expected, stop here.
Run the UPDATE or DELETE with the same WHERE. The database reports how many rows it changed, and that must match the preview.
Query the result: row counts, totals, a couple of rows by eye. Compare with the numbers from before the change.
Only then build the table other people will read, and say what it contains.

The same loop applies whether you clean a supplier file or publish a monthly summary: copy, preview, change, verify, publish. Verifying is a query, not a feeling. Compare counts and totals with what you had before, and look at the edge rows you expected to change.

Common misconceptions

"I'll be careful, so I don't need a preview"

Care does not show you which rows a condition matches. A WHERE with OR mixed with AND, or a text comparison that differs in case, matches rows you did not picture. The preview shows the actual rows, and the count shows the size of the change.

"DELETE and TRUNCATE do the same thing"

They both empty rows, but DELETE can take a WHERE, is logged row by row and can be rolled back in a transaction. TRUNCATE takes no WHERE and is built to clear the whole table. Neither removes the table, which is DROP.

"CREATE TABLE AS copies the table"

It copies the result of a query: the rows and column types, not the primary key, indexes or defaults. If a copy needs those, create the table with a full definition and then use INSERT INTO … SELECT.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

You must delete the 24 order lines with quantity 0 or below from your staging copy. What is the safest order of steps? DELETE first, then check the row count that comes back TRUNCATE the table, then insert the good rows again Run SELECT COUNT(*) with the same WHERE, confirm 24, then DELETE with that WHERE DROP the table and rebuild it from the original without those rows