DataslopeInserting, Updating, and Deleting Data
How rows get into a table and how they change — INSERT, UPDATE, and DELETE — and why WHERE is the most important word on this page.
A table is only useful once it holds data. This page covers the
three statements that change a table's contents: INSERT (add
rows), UPDATE (change rows), and DELETE (remove rows). Together
with SELECT, these four verbs cover almost everything an
application ever does to data.
INSERT: adding rows
INSERT puts new rows into a table. You name the columns, then
supply matching values:
A few things to absorb:
- The column list
(title, done)and the values must line up in the same order. - We left out
id(it isSERIAL, so Postgres fills it) and sometimesdone(itsDEFAULT falsekicks in). - You can insert many rows in one statement by listing several
(...)groups.
UPDATE: changing existing rows
UPDATE changes values in rows that already exist. You set new
values with SET, and — crucially — you say which rows with
WHERE:
Only the "Buy milk" row changed, because WHERE title = 'Buy milk'
selected exactly that row.
DELETE: removing rows
DELETE removes rows. Again, WHERE decides which ones:
The two finished tasks are gone; the unfinished one remains.
The most important warning in this course
UPDATE and DELETE without a WHERE clause affect every row in
the table. This is the single most common way beginners
accidentally destroy data.
Always think about WHERE first
Before you run any UPDATE or DELETE, ask: "which rows do I want
to change?" and write the WHERE clause to match. A reliable habit
is to first run a SELECT with the same WHERE to see exactly which
rows you are about to affect, then swap SELECT * for your UPDATE
or DELETE.
Preview before you change
Practice the safe habit right here. First the SELECT shows you the
target rows; only then do you change them.
This "preview with SELECT, then act" rhythm will save you from
painful mistakes for the rest of your data career.
Check your understanding
What happens if you run DELETE FROM tasks; with no WHERE clause?
It deletes only the most recently added row.
It does nothing because a WHERE is required.
It deletes every row in the tasks table.
It deletes the table's structure as well as its rows.
Which clause determines which rows an UPDATE or DELETE affects?
SET
WHERE
VALUES
FROM
Why is it a good habit to run a SELECT with your WHERE condition before running a matching DELETE?
Because DELETE does not work unless a SELECT ran first.
To preview exactly which rows the condition matches, so you don't delete more than you intend.
Because SELECT automatically backs up the rows.
Because it makes the DELETE run faster.