Skip to content
Ameen Abozaid
Englishالعربية
PublishedBEG-050-L03

INSERT, UPDATE, DELETE

  • Lesson 3 of 9
  • 11 min read
  • Beginner
On This Lesson 11 topics

Lesson Overview

The shelves from Lesson 2 are built but empty. This lesson is about moving the books: putting rows in with INSERT, correcting them with UPDATE, and removing them with DELETE. These three are the writing half of DML.

They come with one rule that this whole lesson is built around: UPDATE and DELETE act on every row unless a WHERE clause narrows them down. A librarian who is told "correct the card" does it for one card; a database told "change the country" with no condition does it for every card in the drawer.

In the library picture, the constraints you wrote in Lesson 2 are the librarian's rules: they will not shelve a book without a title, and will not accept an author who does not exist. This lesson shows those rules in action.

This lesson builds on Lessons 1 and 2 (DML versus DDL, tables, NOT NULL, foreign keys).

Learning Objectives

By the end of this lesson you will be able to:

  • Insert one or several rows with INSERT INTO ... VALUES, naming the columns.
  • Update chosen rows with UPDATE ... SET ... WHERE.
  • Delete chosen rows with DELETE FROM ... WHERE.
  • Explain what happens to UPDATE and DELETE without WHERE.
  • Use the "SELECT first" habit and read the row count after each statement.
  • Read the errors raised by primary key, NOT NULL, and foreign key constraints.

Written Explanation

INSERT

sql
INSERT INTO authors (id, name, country) VALUES (1, 'Ursula Le Guin', 'USA');

The first parentheses list the columns; the second list the values, in the same order. Text and dates are in single quotes, numbers are not, and NULL is written without quotes.

Three useful facts, all from the PostgreSQL manual's page on inserting data:

  • List the column names. Without a list, values must follow the table's own column order, so a later ALTER can silently break the statement. Many users consider listing the columns good practice.
  • Omitted columns get their default, or NULL if there is no default. INSERT INTO authors (id, name) VALUES (2, 'Chinua Achebe'); leaves country empty.
  • Several rows at once are allowed: separate the value lists with commas.

If a row breaks a rule from the table definition, the whole statement is rejected and nothing is inserted.

UPDATE

sql
UPDATE authors SET country = 'Nigeria' WHERE id = 2;

SET names the columns to change and their new values (several can be separated by commas, and a value can use the old one, as in published_year = published_year + 1). WHERE picks the rows. The manual says it plainly: only rows matching the condition are updated, and when the clause is omitted, all rows in the table are updated.

DELETE

sql
DELETE FROM authors WHERE id = 10;

Same shape: WHERE picks the rows to remove. The manual's warning for the no-WHERE form is brief: all rows in the table will be deleted. The table itself stays; only the rows go (compare DROP TABLE, which is DDL).

WHERE in one minute

You will learn WHERE fully in Lesson 4. For now: WHERE is a condition; rows for which it is true are affected. The safest condition is one on the primary key, WHERE id = 2, because the key matches at most one row. Conditions on other columns, such as WHERE name = 'Anna', can match several rows or none. Matching zero rows is not an error: the statement succeeds and changes nothing, which is easy to miss.

The safe habit

Before any UPDATE or DELETE:

  1. Write a SELECT with the same WHERE: SELECT * FROM authors WHERE id = 2;
  2. Look at what comes back. Is it the rows you expect, and how many?
  3. Replace SELECT * with UPDATE ... SET ... or DELETE, keeping the WHERE exactly as it was.
  4. After running, compare the row count in the tag (UPDATE 1) with what you saw in step 2.

Many people also write the WHERE first and the SET second when composing, so that a half-typed statement can never run without its condition.

Constraints guard the door

The rules from Lesson 2 now do real work:

You try toRule that stops you
Insert a second row with an existing idPrimary key: must be unique
Insert a row without a required valueNOT NULL
Insert a book for an author that does not existForeign key
Delete an author who still has booksForeign key (a row is "still referenced")

The last one matters for deletes: you must remove or reassign the dependent rows first, children before parents. (Foreign keys can be configured to cascade deletes automatically, but that is not the default, and we do not use it in this module.)

Examples

All examples were run on PostgreSQL 18.3 with psql on the Lesson 2 library tables, starting empty.

Example 1: inserting rows (simple)

sql
INSERT INTO authors (id, name, country) VALUES (1, 'Ursula Le Guin', 'USA');
INSERT INTO authors (id, name) VALUES (2, 'Chinua Achebe');
INSERT INTO authors (id, name, country) VALUES
  (3, 'Naguib Mahfouz', 'Egypt'), (4, 'Octavia Butler', 'USA');
SELECT * FROM authors;

The tags were INSERT 0 1, INSERT 0 1, and INSERT 0 2 (the second number is the row count). The result:

text
 id |      name      | country 
----+----------------+---------
  1 | Ursula Le Guin | USA
  2 | Chinua Achebe  | 
  3 | Naguib Mahfouz | Egypt
  4 | Octavia Butler | USA
(4 rows)

Row 2 has an empty country: it is NULL. The apostrophe in a name such as O'Brien is written twice inside the quotes: 'Flann O''Brien'.

Example 2: updating with the safe habit (practical)

Chinua Achebe has no country. Look first:

sql
SELECT * FROM authors WHERE country IS NULL;
text
 id |     name      | country 
----+---------------+---------
  2 | Chinua Achebe | 
 10 | Flann O'Brien | 
(2 rows)

(Lesson 4 explains IS NULL.) Two rows lack a country, so a careless condition could touch both. We want only id 2:

sql
UPDATE authors SET country = 'Nigeria' WHERE id = 2;
text
UPDATE 1

One row, as planned. Now the same statement without the WHERE, but with a different value:

sql
UPDATE authors SET country = 'Unknown';
SELECT * FROM authors ORDER BY id;
text
UPDATE 5
 id |      name      | country 
----+----------------+---------
  1 | Ursula Le Guin | Unknown
  2 | Chinua Achebe  | Unknown
  3 | Naguib Mahfouz | Unknown
  4 | Octavia Butler | Unknown
 10 | Flann O'Brien  | Unknown
(5 rows)

UPDATE 5 was the warning: there were five authors. The database did exactly what was written, and the real countries are overwritten. Nothing prompted us to confirm. (This ran on practice data. On a real database, recovery would need a backup, which is why the habit is prevention.)

Example 3: deleting and the foreign key (realistic)

sql
DELETE FROM authors WHERE id = 1;
text
ERROR:  update or delete on table "authors" violates foreign key constraint "books_author_id_fkey" on table "books"
DETAIL:  Key (id)=(1) is still referenced from table "books".

The database refuses: a book still points at author 1. Remove the child first, then the parent:

sql
DELETE FROM books WHERE id = 1;
DELETE FROM authors WHERE id = 1;
DELETE FROM authors WHERE id = 99;
text
DELETE 1
DELETE 1
DELETE 0

The last statement matched no row, so it returned DELETE 0: no error, no change. Had you meant to delete an existing author, 0 would be your clue that the condition was wrong.

Finally, the dangerous form. DELETE FROM authors; (no WHERE) was refused here only because books still referenced the authors. After deleting from books too, the unconditional delete worked:

text
DELETE FROM books;     -> DELETE 2
DELETE FROM authors;   -> DELETE 3

Every row went, and SELECT count(*) FROM authors; returned 0. The table still exists with its columns.

Backend Insight

In an application, you almost never type UPDATE by hand. Code builds it, with the WHERE id = ? condition filled in at run time from the request: "update this order's status", "delete this user's session". A bug that drops or mangles the WHERE clause turns a one-row change into a table-wide one, so teams review these statements carefully and test them against a copy of the data.

The UPDATE 5 / DELETE 0 row counts matter in code, too. Database libraries return the count, and good code checks it: "I expected to update one order and it updated none" is a real, detectable condition (a missing record), not a silent success. You will see this when you learn how Java talks to a database.

Common Mistakes

Mistake 1: UPDATE or DELETE without WHERE

  • Incorrect: UPDATE members SET name = 'Amal';
  • Correct: UPDATE members SET name = 'Amal' WHERE id = 1;

Every row is changed or deleted. Run the matching SELECT first and check the row count afterwards.

Mistake 2: A WHERE on a non-unique column

WHERE name = 'Amal' is fine only if exactly one member has that name. Prefer the primary key when you mean one row.

Mistake 3: Inserting without a column list

INSERT INTO authors VALUES (5, 'Lu Xun', 'China'); depends on the column order. PostgreSQL accepts the form as an extension to standard SQL, but listing the columns is safer when the table changes.

Mistake 4: Deleting a parent before its children

The foreign key rejects it, as in Example 3. Delete or reassign the child rows first.

Mistake 5: Reading UPDATE 0 as success

Zero rows is a legal result. If you expected a change, the condition matched nothing.

Exercises

Try each exercise yourself before opening the solutions.

Level A: Foundation

A1. Write an INSERT for a member with id 6, name 'Farah', joined on '2025-03-01'.

Show solution A1

A1. INSERT INTO members (id, name, joined_on) VALUES (6, 'Farah', '2025-03-01');

A2. Write an UPDATE that sets published_year to 1969 for the book with id 3.

Show solution A2

A2. UPDATE books SET published_year = 1969 WHERE id = 3;

A3. Write a DELETE that removes the member with id 6.

Show solution A3

A3. DELETE FROM members WHERE id = 6; (If member 6 has loans, remove those first.)

Level B: Application

B1. Insert two authors, ids 20 and 21, in a single statement. What tag do you expect?

Show solution B1

B1. INSERT INTO authors (id, name) VALUES (20, 'Author A'), (21, 'Author B'); The tag is INSERT 0 2.

B2. Before running UPDATE books SET published_year = NULL WHERE author_id = 5;, which statement do you run first, and what two things do you check?

Show solution B2

B2. Run SELECT * FROM books WHERE author_id = 5; first. Check that the returned rows are the ones you mean to change, and note how many there are, so you can compare it with the count in the UPDATE tag.

B3. An insert into books fails with Key (author_id)=(42) is not present in table "authors". What went wrong, and what are two ways to fix it?

Show solution B3

B3. The book refers to author 42, who does not exist, so the foreign key rejects it. Fix it by inserting author 42 first, or by changing the author_id to an existing author.

Level C: Challenge

C1. A colleague runs DELETE FROM loans WHERE returned_on = NULL; hoping to remove all returned loans. It reports DELETE 0. Explain the likely reason, and what to run instead after you check the matching rows. (Lesson 4 covers NULL tests, so you may need to peek ahead.)

Show solution C1

C1. In SQL, a comparison with NULL using = is never true, so no row matched and nothing was deleted. The correct test is IS NULL (rows still not returned), or IS NOT NULL for rows that were returned. The colleague should first run SELECT * FROM loans WHERE returned_on IS NOT NULL;, check the rows, and then reuse the same condition in DELETE. Deleting loan history is a design decision in its own right, so confirm it is wanted.

C2. Write a safe procedure, in numbered steps, for changing the due date of all loans of member 2 by one week. State which row count you expect to see change.

Show solution C2

C2.

  1. SELECT id, due_on FROM loans WHERE member_id = 2; and note the count and dates.
  2. Write UPDATE loans SET due_on = due_on + 7 WHERE member_id = 2;. Adding an integer to a DATE adds days in PostgreSQL.
  3. Run it and confirm the tag's count equals the count from step 1.
  4. Re-run the SELECT to see the new dates.

Date arithmetic like due_on + 7 is PostgreSQL behaviour (checked on 18.3); other databases write it differently.

Mini Challenge

You are the librarian for a day. Starting from an empty members table, do these in order and write the row count shown after each statement.

  1. Insert three members (ids 1 to 3) in one statement.
  2. Insert a fourth member whose name contains an apostrophe.
  3. Correct member 2's name with a safe UPDATE.
  4. Delete member 4.
  5. Deliberately run an UPDATE without WHERE on this practice table, then describe what you would have needed to recover.

Check your work: the counts should be INSERT 0 3, INSERT 0 1, UPDATE 1, DELETE 1, and the unconditional update should report the number of rows then in the table (3). Your step 5 note should mention a backup, not "undo".

Summary & Next Lesson

Key takeaways

  • INSERT INTO t (cols) VALUES (...) adds rows; list the columns, and several rows can be added in one statement.
  • UPDATE ... SET ... WHERE ... changes rows; DELETE FROM ... WHERE ... removes them.
  • Without WHERE, UPDATE and DELETE affect every row.
  • Habit: run a SELECT with the same WHERE first, then compare the row count.
  • Constraints reject duplicate keys, missing required values, and orphaned references; delete children before parents.
  • A row count of 0 is not an error, but it may mean your condition is wrong.

Next lesson

You have been using SELECT and WHERE informally. The next lesson, "SELECT, WHERE, ORDER BY", covers reading data properly: choosing columns, filtering with conditions, sorting, limiting results, and handling NULL.

Self-check

  1. What does DELETE FROM books; do?
  2. Why list the columns in an INSERT?
  3. What does the tag UPDATE 0 tell you?
  4. Why can DELETE FROM authors WHERE id = 1; fail?
  5. What is the safe habit before an UPDATE?
Show answers
  1. It deletes every row of books; the table remains.
  2. The statement keeps working if the table's column order changes, and it is easier to read.
  3. The statement ran but matched no rows, so nothing changed.
  4. Books may still reference that author through a foreign key.
  5. Run a SELECT with the same WHERE first and check the rows and their count.

References

  • PostgreSQL documentation, Inserting Data. Supports listing columns as good practice, omitted columns taking defaults, multi-row VALUES, and the column-list-free form being a PostgreSQL extension.
  • PostgreSQL documentation, Updating Data. Supports WHERE limiting updated rows, all rows being updated when it is omitted, and zero matching rows not being an error.
  • PostgreSQL documentation, Deleting Data. Supports a DELETE without a condition removing all rows.
  • Error messages and row-count tags are copied from real runs on PostgreSQL 18.3. The remark that foreign keys can be configured to cascade deletes is general knowledge that I did not verify on a fetched page; = NULL matching nothing was observed in a real run and is explained in Lesson 4.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson

Type to search levels, phases, modules and lessons.