Lesson 06 · SQL

INSERT, UPDATE and DELETE

SELECT only reads a table. Three SQL commands change it: INSERT adds rows, UPDATE changes values in rows already there, and DELETE removes rows. Follow all three through a small library's books table, see how WHERE picks the rows a change touches, read the reply that counts them (INSERT 0 1, UPDATE 1, DELETE 2), and learn why a missing WHERE changes every row and how BEGIN and ROLLBACK let you take a change back.

12 min11 steps5 questionsBeginner

Fig 6 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01Which statement adds the book Piranesi with 1 copy as a new row of books(id, name, copies)?

INSERT INTO adds new rows. The column list says which columns to fill and VALUES gives one value for each, in the same order. Text goes in single quotes, so Piranesi without quotes would be read as a column name and fail. The UPDATE would change every existing row instead.

02The table holds Dune 2, Coraline 0, Holes 1, The Road 3 and Wonder 0 copies. What does UPDATE books SET copies = copies + 1 WHERE copies = 0; do?

WHERE is tested on every row and is true only for Coraline and Wonder. SET reads each matching row's old value (0), adds 1 and stores 1. The other rows are left as they were, and the reply counts the 2 rows changed.

03After DELETE FROM books WHERE copies = 0; PostgreSQL replies DELETE 2. What does the 2 mean?

The command tag names the command and the number of rows it affected. DELETE always removes whole rows, never single columns. INSERT and UPDATE reply the same way, for example INSERT 0 1 and UPDATE 1.

04What happens if you run UPDATE books SET copies = 0; with no WHERE?

WHERE is optional. Without it, UPDATE (and DELETE) act on every row of the table. That is why it pays to check the WHERE before you run one, or to run it after BEGIN so you can still ROLLBACK.

05You type BEGIN; then an UPDATE that changes 4 rows, then ROLLBACK;. What does the table hold now?

BEGIN starts a transaction. ROLLBACK cancels all of its changes, while COMMIT would keep them. Without BEGIN, PostgreSQL keeps each statement as soon as it succeeds.

Key terms

Glossary

INSERT: The SQL command that adds new rows to a table: INSERT INTO table (columns) VALUES (values).
UPDATE: The SQL command that changes values in rows already in a table: UPDATE table SET column = value WHERE condition.
DELETE: The SQL command that removes whole rows from a table: DELETE FROM table WHERE condition.
WHERE clause: The condition that picks which rows a statement reads or changes. It is tested on every row; without it, UPDATE and DELETE affect all rows.
SET clause: The part of an UPDATE that says which columns get which new values. A new value may use the old one, as in copies = copies + 1.
Command tag: The short reply a database such as PostgreSQL sends after a statement, such as INSERT 0 1, UPDATE 1 or DELETE 2: the command and how many rows it affected.
Transaction: A group of statements that is kept or cancelled as one. BEGIN starts it, COMMIT keeps its changes and ROLLBACK cancels them.

Fine print

Notes and sources

Without ORDER BY, SQL does not promise any order for the rows of a result. The figure keeps the rows in id order; the last query asks for that with ORDER BY id.
In INSERT 0 1 the 0 is a leftover: it used to be an internal object id and is now always 0. The 1 is the number of rows inserted. psql shows these replies; drivers give you the row count.
You may leave out the column list and give values for every column in table order, but naming the columns is clearer and keeps working if the table gains a column later.
Without BEGIN, PostgreSQL (and SQLite) treat each statement as its own transaction and keep it as soon as it succeeds. Python’s sqlite3 module opens a transaction for you before a change, so call commit() to keep your changes.
Every statement in this lesson runs unchanged on both PostgreSQL and SQLite.
The Python example uses the sqlite3 module that ships with Python. Cursor.rowcount is the number of rows the last INSERT, UPDATE or DELETE affected: it prints 1, 1 and 2, then the 4 rows.

Quiz Unlocks after the last step.