SQL course · Module 5: Creating and changing data · Lesson 17 of 40
Quick answer: SQL INSERT adds rows, UPDATE changes rows and DELETE removes rows. Always list the columns in an INSERT, and always give UPDATE and DELETE a WHERE: without it, they change or remove every row in the table. Run the same WHERE in a SELECT first to see which rows you will touch. In PostgreSQL, add RETURNING to see the rows you just changed, for example INSERT ... RETURNING id to get a new row’s id.
In this lesson you will learn:
- how to write SQL INSERT for one row and for many rows
- how to UPDATE and DELETE only the rows you mean
- how
RETURNINGshows the changed rows without a second query - why UPDATE or DELETE without
WHEREis dangerous, and a simple safety net - when to use TRUNCATE instead of DELETE
Before you start: Lesson 5, SQL WHERE Clause: Filter Rows with AND, OR, IN, BETWEEN and LIKE and Lesson 16, SQL CREATE TABLE: Data Types and Constraints.
We ran every example on PostgreSQL 16.15 with the shop database, in order, and checked the current PostgreSQL 18 manual. This lesson changes the shop data, and the new ids you see depend on it. Start psql in the folder that holds shop.sql from Lesson 3 and run \i shop.sql before you begin, so your ids match ours. At the end you reset the data the same way.
Table of Contents
SQL INSERT: add a row
List the columns after the table name, then give the values in the same order:
INSERT INTO customers (name, email, city, country, signup_date)
VALUES ('Sara Khan', 'sara.khan@example.com', 'Islamabad', 'Pakistan', '2026-11-01')
RETURNING id, name;
id | name
----+-----------
11 | Sara Khan
(1 row)
INSERT 0 1
We left out id. It is an identity column (Lesson 16), so PostgreSQL gave Sara the next number, 11. RETURNING id, name printed the new row, so we did not need a second query to find her id. INSERT 0 1 means one row was inserted.
Why you should list the columns
The PostgreSQL manual says “many users consider it good practice to always list the column names” (PostgreSQL docs: Inserting Data). Without a list, the values go into the columns in table order, starting with id:
INSERT INTO customers VALUES ('Ali Raza', NULL, 'Quetta', 'Pakistan', '2026-11-02');
ERROR: invalid input syntax for type integer: "Ali Raza"
LINE 1: INSERT INTO customers VALUES ('Ali Raza', NULL, 'Quetta', 'P...
^
Here the error saved us. If the types had matched, the values would have gone into the wrong columns silently. A column list also keeps working when someone adds a column to the table later.
Insert several rows at once
One INSERT can add many rows. Separate them with commas:
INSERT INTO products (name, category, price)
VALUES ('Monitor Arm', 'Accessories', 64.00),
('Sticky Notes', 'Stationery', 3.25)
RETURNING *;
id | name | category | price
----+--------------+-------------+-------
11 | Monitor Arm | Accessories | 64.00
12 | Sticky Notes | Stationery | 3.25
(2 rows)
INSERT 0 2
RETURNING * returns every column of the new rows. For big loads, the manual suggests the COPY command, which “is not as flexible as the INSERT command, but is more efficient”.
SQL UPDATE: test the WHERE with SELECT first
Say all Stationery prices go up by 10%. Before you change anything, run the WHERE part in a SELECT:
SELECT id, name, price FROM products WHERE category = 'Stationery' ORDER BY id;
id | name | price
----+------------------+-------
6 | Notebook Pack | 12.50
7 | Gel Pens 10-Pack | 6.75
12 | Sticky Notes | 3.25
(3 rows)
Three rows, the right ones. Now replace SELECT ... FROM with UPDATE ... SET and keep the same WHERE:
UPDATE products
SET price = price * 1.10
WHERE category = 'Stationery'
RETURNING id, name, price;
id | name | price
----+------------------+-------
6 | Notebook Pack | 13.75
7 | Gel Pens 10-Pack | 7.43
12 | Sticky Notes | 3.58
(3 rows)
UPDATE 3
price * 1.10 uses the old value of each row. With an UPDATE, RETURNING shows the new values. The column is numeric(10,2), so 7.425 was rounded to 7.43. UPDATE 3 is the number of rows changed. It should match the count from your SELECT.
To change several columns, separate the assignments with commas. A WHERE on the primary key changes exactly one row:
UPDATE customers
SET city = 'Rawalpindi', email = 'sara.k@example.com'
WHERE id = 11
RETURNING id, name, email, city;
id | name | email | city
----+-----------+--------------------+------------
11 | Sara Khan | sara.k@example.com | Rawalpindi
(1 row)
UPDATE 1
If no row matches, nothing happens and there is no error:
UPDATE customers SET city = 'Multan' WHERE id = 999;
UPDATE 0
The manual says: “It is not an error to attempt an update that does not match any rows” (PostgreSQL docs: Updating Data). Check the number after UPDATE: 0 often means a typo in the WHERE.
UPDATE and DELETE without WHERE
If you leave out WHERE, the manual says “all rows in the table are updated”. PostgreSQL does not ask “are you sure?”. To show it safely, we start a transaction with BEGIN, so we can undo the change with ROLLBACK (Lesson 19 explains transactions):
BEGIN;
UPDATE customers SET city = 'Lahore';
SELECT city, count(*) FROM customers GROUP BY city;
BEGIN
UPDATE 11
city | count
--------+-------
Lahore | 11
(1 row)
The UPDATE reported UPDATE 11: every customer now lives in Lahore. Undo it:
ROLLBACK;
SELECT city, count(*) FROM customers GROUP BY city ORDER BY city;
ROLLBACK
city | count
------------+-------
Berlin | 1
Chicago | 1
Karachi | 1
Lahore | 2
London | 1
Milan | 1
Paris | 1
Rawalpindi | 1
Toronto | 1
(null) | 1
(10 rows)
The cities are back, including Sara’s Rawalpindi and Hira’s NULL (the GROUP BY from Lesson 10 puts all NULLs in one group). DELETE without WHERE is the same: inside a test transaction, DELETE FROM order_items; reported DELETE 40, all order lines. The manual’s comment on DELETE FROM products; is: “then all rows in the table will be deleted! Caveat programmer” (PostgreSQL docs: Deleting Data).
Outside a transaction there is no undo. Good habits:
- Write the statement as a
SELECTfirst, then turn it into the UPDATE or DELETE. - Compare the row count after
UPDATEorDELETEwith the count from yourSELECT. - On important data, run
BEGIN;first, check the result, thenCOMMIT;orROLLBACK;.
MySQL users have one more option: the mysql client’s --safe-updates mode makes UPDATE and DELETE fail unless the WHERE uses a key or the statement has a LIMIT (MySQL docs: mysql Client Tips).
SQL DELETE with RETURNING
The same habit works for DELETE. First the SELECT:
SELECT id, name FROM products WHERE id > 10;
id | name
----+--------------
11 | Monitor Arm
12 | Sticky Notes
(2 rows)
Then the DELETE with the same WHERE. RETURNING shows the rows as they were before they were removed:
DELETE FROM products
WHERE id > 10
RETURNING id, name;
id | name
----+--------------
11 | Monitor Arm
12 | Sticky Notes
(2 rows)
DELETE 2
Foreign keys protect related rows. Customer 1 still has orders, so this fails, as you saw in Lesson 1:
DELETE FROM customers WHERE id = 1;
ERROR: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL: Key (id)=(1) is still referenced from table "orders".
One more detail: deleted ids are not used again. Delete Sara, add a new customer, and look at the id:
DELETE FROM customers WHERE id = 11 RETURNING id, name;
id | name
----+-----------
11 | Sara Khan
(1 row)
DELETE 1
INSERT INTO customers (name, country, signup_date)
VALUES ('Ali Raza', 'Pakistan', '2026-11-02')
RETURNING id, name;
id | name
----+----------
12 | Ali Raza
(1 row)
INSERT 0 1
Ali got 12, not 11. The columns we left out (email, city) became NULL, because they have no default.
TRUNCATE vs DELETE
TRUNCATE empties whole tables. The manual says it “has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster”, and it “reclaims disk space immediately” (PostgreSQL docs: TRUNCATE).
| DELETE | TRUNCATE | |
|---|---|---|
| removes | rows that match WHERE (or all) | all rows, no WHERE |
| speed on big tables | scans every row | faster, does not scan |
| RETURNING | yes | no |
| identity numbers | keep counting | keep counting, or start again with RESTART IDENTITY |
| referenced by a foreign key | fails only for rows still referenced | fails unless the referencing tables are truncated too |
In our test, TRUNCATE order_items RETURNING *; gave a syntax error. The foreign key row in action:
TRUNCATE products;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "order_items" references "products".
HINT: Truncate table "order_items" at the same time, or use TRUNCATE ... CASCADE.
The manual warns about CASCADE: “be very careful when using this option, or else you might lose data you did not intend to!” Use DELETE for everyday work, and TRUNCATE when you really want to empty a large table.
Try it yourself
Do the exercises right after the examples above, before you reset the data; the ids in the solutions assume that order.
Exercise 1: Add a product
Insert a product called Desk Mat in the Accessories category for 19.99, and return its new id.
Exercise 2: Ship order 22
Order 22 is pending. Check it with a SELECT, then change its status to shipped and return the id and the new status.
Exercise 3: Remove the Desk Mat
Delete the product you added in Exercise 1, returning its id, name and price.
Solutions
Solution 1
INSERT INTO products (name, category, price)
VALUES ('Desk Mat', 'Accessories', 19.99)
RETURNING id;
id
----
13
(1 row)
INSERT 0 1
The id is 13 because 11 and 12 were used by the products we deleted.
Solution 2
SELECT id, status FROM orders WHERE id = 22;
UPDATE orders SET status = 'shipped' WHERE id = 22 RETURNING id, status;
id | status
----+---------
22 | pending
(1 row)
id | status
----+---------
22 | shipped
(1 row)
UPDATE 1
The SELECT showed pending; UPDATE 1 confirms exactly one order changed.
Solution 3
DELETE FROM products WHERE name = 'Desk Mat' RETURNING id, name, price;
id | name | price
----+----------+-------
13 | Desk Mat | 19.99
(1 row)
DELETE 1
RETURNING shows the row as it was just before it was deleted.
When you finish, reset the data: the lesson and the exercises changed prices, customers and an order. Run \i shop.sql in psql again. It prints SET, BEGIN, four DROP TABLE and four CREATE TABLE lines, INSERT 0 10, INSERT 0 10, INSERT 0 22, INSERT 0 40 and COMMIT, as in Lesson 3.
Common mistakes
- UPDATE or DELETE without WHERE. Every row changes:
UPDATE 11,DELETE 40. Test theWHEREwithSELECTfirst. - INSERT without a column list. Values go into the wrong columns, or you get an error like
invalid input syntax for type integer: "Ali Raza". - Expecting UPDATE to skip constraints. Rules from Lesson 16 still apply:
UPDATE products SET price = -1 WHERE id = 1;failed withnew row for relation "products" violates check constraint "products_price_check". - Ignoring
UPDATE 0. It is not an error, so a typo in theWHEREcan go unnoticed. - Trying
TRUNCATE ... WHERE. TRUNCATE has noWHERE; it gavesyntax error at or near "WHERE". Use DELETE to remove some rows.
Frequently asked questions
Can I undo an UPDATE or DELETE?
Only if you started a transaction with BEGIN and have not run COMMIT yet: then ROLLBACK undoes it, as shown above. After the change is saved, there is no undo command. You need a backup, or, for this course, \i shop.sql. Lesson 19 covers transactions.
What is the difference between DELETE and TRUNCATE?
DELETE removes the rows that match a WHERE (or all rows) and can return them with RETURNING. TRUNCATE always empties the whole table, is faster on big tables, and cannot run while other tables reference the table unless they are truncated too.
Does RETURNING work in MySQL and SQLite?
SQLite supports RETURNING “since version 3.35.0” and models it on PostgreSQL (SQLite docs: RETURNING); it worked in our SQLite 3.53.1 test. The MySQL 8.4 INSERT syntax has no RETURNING clause; to get a new AUTO_INCREMENT id, MySQL offers LAST_INSERT_ID() (MySQL docs: INSERT). PostgreSQL 18 added old and new to RETURNING, so an UPDATE can return the value before and after the change (PostgreSQL 18 release notes). On our PostgreSQL 16, RETURNING old.price failed with missing FROM-clause entry for table "old".
Summary
- SQL INSERT adds rows; list the columns, and add several rows with one
VALUESlist. - UPDATE changes rows and DELETE removes them; both affect every row when
WHEREis missing. - Test the
WHEREwithSELECTfirst, and compare the row count PostgreSQL reports. RETURNINGshows the inserted, updated or deleted rows, including new ids.- TRUNCATE empties whole tables quickly; use DELETE when you need a
WHEREorRETURNING.
In a data pipeline, the load step writes rows into tables, for example with INSERT. The first project in the data engineering projects list loads processed data into PostgreSQL, a good place to use what you learned here.
Next lesson
Next: Lesson 18, SQL Upsert: INSERT … ON CONFLICT and MERGE.
Sources
- PostgreSQL documentation: Inserting Data
- PostgreSQL documentation: Updating Data
- PostgreSQL documentation: Deleting Data
- PostgreSQL documentation: Returning Data from Modified Rows
- PostgreSQL documentation: TRUNCATE
- PostgreSQL 18 release notes (OLD and NEW in RETURNING)
- MySQL 8.4 Reference Manual: INSERT Statement
- MySQL 8.4 Reference Manual: mysql Client Tips (safe-updates mode)
- SQLite documentation: RETURNING





