Difficulty: Beginner to Intermediate | Prerequisites: Part 1 (Query Foundations) and Part 2 (Table Construction). You need to understand WHERE clauses and table constraints before modifying data.
Querying reads data. This section is about writing data: adding new rows with INSERT, changing existing rows with UPDATE, and removing rows with DELETE. These three operations, together with SELECT, form the four fundamental SQL operations (often called CRUD: Create, Read, Update, Delete). Every web form submission, every account edit, every "remove from cart" button triggers one of these statements. The WHERE clause you learned in Part 1 is critical here, because UPDATE and DELETE without a WHERE clause affect every row in the table.
INSERT INTO adds new rows to a table, either one at a time, in bulk, or from a SELECT query on another table. UPDATE modifies existing data and uses the same WHERE clause as SELECT to target specific rows. DELETE FROM removes rows, again using WHERE to target. All three must respect table constraints (primary keys, foreign keys, NOT NULL, UNIQUE, CHECK). Forgetting the WHERE clause on UPDATE or DELETE affects every row in the table.
INSERT INTO
The SQL statement that adds one or more new rows to an existing table. You specify the table, the columns, and the values.
VALUES
The keyword used in an INSERT statement to specify the data being added. Each set of values is enclosed in parentheses.
SERIAL / auto-increment
A column with the SERIAL data type does not need a value in the INSERT statement. The database generates the next sequential value automatically. In simple terms, the database numbers each new row for you.
RETURNING
A PostgreSQL-specific clause that outputs the affected rows immediately after an INSERT, UPDATE, or DELETE. Useful for confirming what changed. Think of it as a built-in "show me what just happened."
UPDATE
The SQL statement that modifies existing data in a table. Uses SET to define new values and WHERE to target specific rows.
SET
The clause in an UPDATE statement that specifies which columns to change and what their new values should be.
DELETE FROM
The SQL statement that removes rows from a table. Uses WHERE to target specific rows.
INSERT INTO ... SELECT
A variation of INSERT that populates a table using the result set of a SELECT query, instead of manually listing values. Useful for copying or summarising data from other tables.
COALESCE
A function that returns the first non-NULL argument. Used with aggregates to substitute 0 for NULL results.
Basic syntax:
INSERT INTO artist (artist_id, name)
VALUES (1000, 'Bob Dylan');
Column order in the INSERT does not have to match the table's column order, but the VALUES must match the column list:
INSERT INTO artist (name, artist_id)
VALUES ('Michael Jackson', 1001);
Swapping the values while keeping the column order can cause errors or silent data corruption:
-- ERROR (string into integer column):
INSERT INTO artist (name, artist_id)
VALUES (1001, 'Michael Jackson');
All columns with NOT NULL or PRIMARY KEY constraints must be included, unless they have a DEFAULT value or use SERIAL
Foreign key values must exist in the referenced table:
-- This fails if artist_id 999 does not exist in the artist table:
INSERT INTO album (artist_id, album_id, title)
VALUES (999, 2000, 'Latest Hits');
When a column uses SERIAL, you can omit it from the INSERT:
INSERT INTO contact (username, password)
VALUES ('Mustang', 'Password3');
-- contact_id is generated automatically
You can also explicitly use the DEFAULT keyword:
INSERT INTO contact (contact_id, username, password)
VALUES (DEFAULT, 'Caloric', 'Password77');
Or call the sequence function directly (rarely needed):
INSERT INTO contact (contact_id, username, password)
VALUES (nextval('contact_contact_id_seq'), 'sophia', 'Password1');
The sequence name follows the pattern: tablename_columnname_seq
List multiple value sets separated by commas:
INSERT INTO referral (first_name, last_name, email)
VALUES
('Randall', 'Faustino', 'r.faust@email.com'),
('Park', 'Deanna', 'p.deanna@email.com'),
('Sunil', 'Carrie', 's.carrie@email.com'),
('Jon', 'Brianna', 'j.brianna@email.com');
Missing commas between value sets produces a syntax error
More efficient than running separate INSERT statements for each row
RETURNING * shows all inserted rows immediately (PostgreSQL):
INSERT INTO referral (first_name, last_name, email)
VALUES
('Lana', 'Jakoba', 'l.jakoba@email.com'),
('Tiffany', 'Walker', 't.walk@email.com')
RETURNING *;
You can also return specific columns: RETURNING referral_id;
Replace the VALUES clause with a SELECT query:
INSERT INTO contact (first_name, last_name, phone)
SELECT first_name, last_name, phone
FROM customer
WHERE country = 'USA';
The SELECT column list must match the INSERT column list in order and data type compatibility
Useful for building summary tables:
INSERT INTO invoice_summary (summary_date, all_total, num_of_invoice)
SELECT now(), SUM(total), COUNT(invoice_id)
FROM invoice;
Can load data from any valid SELECT, including those with JOINs, WHERE filters, and aggregates
Basic syntax:
UPDATE referral
SET email = 'r.faustino@email.com'
WHERE referral_id = 2;
Always use the primary key in the WHERE clause when updating a single row. Using other columns (like first_name) risks updating unintended rows if the value is not unique
Update multiple columns at once:
UPDATE customer
SET address = '555 International Parkway',
city = 'Orlando',
state = 'FL',
postal_code = '33133-1111'
WHERE customer_id = 16;
Columns not listed in SET keep their current values
RETURNING * shows the updated row (PostgreSQL):
UPDATE referral
SET email = 'r.faustino@email.com'
WHERE referral_id = 2
RETURNING *;
Without a WHERE clause, UPDATE affects every row:
-- 20% discount on ALL tracks:
UPDATE track
SET unit_price = unit_price * 0.8;
Use the same WHERE conditions you would in a SELECT to target specific rows:
UPDATE track
SET unit_price = unit_price * 0.8
WHERE album_id = 1;
A value can reference itself in the SET clause: unit_price = unit_price * 0.75 takes the current value, multiplies it, and stores the result
Best practice: write and run the WHERE clause as a SELECT first to verify which rows will be affected, then apply it to the UPDATE
RETURNING * is especially valuable for bulk updates to confirm what changed:
UPDATE track
SET unit_price = unit_price * 0.75
WHERE album_id BETWEEN 10 AND 20
RETURNING *;
Basic syntax:
DELETE FROM invoice_line
WHERE invoice_id = 1;
Without a WHERE clause, all rows are deleted:
-- DANGER: removes every row in the table
DELETE FROM invoice_line;
Foreign keys enforce deletion order. You cannot delete a parent row if child rows reference it:
-- This fails if invoice_line rows reference invoice_id = 1:
DELETE FROM invoice
WHERE invoice_id = 1;
-- Correct order:
DELETE FROM invoice_line WHERE invoice_id = 1;
DELETE FROM invoice WHERE invoice_id = 1;
Delete multiple rows using ranges or lists:
DELETE FROM invoice_line
WHERE invoice_id BETWEEN 1 AND 10;
DELETE FROM invoice
WHERE invoice_id > 50;
RETURNING * shows what was removed (PostgreSQL)
Best practice: test your WHERE clause with a SELECT before running DELETE
You can only DELETE FROM one table at a time
The safe deletion sequence (same logic as DROP TABLE order):
Delete from tables with no foreign keys pointing to them (child tables)
Delete from parent tables once children are cleared
Self-referencing UPDATE pattern:
SET column = column * multiplier
This reads the current value, applies the calculation, and writes the result back.
Point-in-time snapshot pattern:
INSERT INTO summary_table (date_col, total_col, count_col)
SELECT now(), SUM(value), COUNT(id)
FROM source_table;
Every user registration form runs an INSERT. Every "edit profile" page runs an UPDATE. Every "delete account" or "remove item" action runs a DELETE. Bulk INSERT with SELECT is how data pipelines load staging tables. Point-in-time summary inserts are how businesses capture daily snapshots of metrics for historical reporting. The RETURNING clause is how web applications confirm to users exactly what changed without running a second query.
Students forget the WHERE clause on UPDATE or DELETE and accidentally modify or remove every row in the table. This is the single most common and most damaging mistake in SQL.
Students assume that swapping two integer values in an INSERT will always produce an error. It will not if both columns accept integers. The database cannot detect that you put the wrong number in the wrong column. These logical errors are silent.
Students think DELETE removes the table. It removes rows. DROP TABLE removes the table itself.
Students forget that INSERT must respect foreign key constraints. You cannot add a child record before its parent exists.
⚠️ Always include a WHERE clause with UPDATE and DELETE unless you genuinely intend to affect every row. Exam questions often test whether you recognise the danger of a missing WHERE.
⚠️ Know the correct order of keywords: INSERT INTO table (columns) VALUES (values).
⚠️ Understand that SERIAL columns can be omitted from the INSERT column list. The database fills them automatically.
⚠️ Be able to write an INSERT ... SELECT statement that copies data from one table to another.
⚠️ Know that foreign key constraints affect the order of deletion: child rows must be deleted before parent rows.
⚠️ Understand the difference between NULL and 0 in the context of UPDATE. Setting a value to NULL is not the same as setting it to 0, especially for aggregate calculations.
True or False: Running UPDATE customer SET email = 'test@test.com'; without a WHERE clause updates only the first row.
Fill in the blank: To add multiple rows in a single INSERT statement, separate each set of values with a ___.
True or False: You can INSERT into a SERIAL column without specifying a value.
Fill in the blank: The ___ keyword in PostgreSQL causes an INSERT, UPDATE, or DELETE to output the affected rows.
True or False: DELETE FROM removes the table from the database.
Answers: 1. False (it updates every row). 2. Comma. 3. True. 4. RETURNING. 5. False (it removes rows; DROP TABLE removes the table).
Q: Write an INSERT statement that adds a new genre with genre_id 1000 and name 'Funk' to the genre table.
A: INSERT INTO genre (genre_id, name) VALUES (1000, 'Funk');
Q: Write an UPDATE statement that changes the email of the customer with customer_id 5 to 'newemail@example.com'.
A: UPDATE customer SET email = 'newemail@example.com' WHERE customer_id = 5;
Q: Why does the following statement fail? DELETE FROM artist WHERE artist_id = 1;
A: Because the album table has a foreign key referencing artist_id. Rows in album that reference artist_id = 1 must be deleted first (and any rows referencing those album rows, such as tracks and invoice lines, must be deleted before that).
Q: Write an INSERT ... SELECT statement that copies the first name, last name, and email of all customers from Canada into a new table called canada_contacts.
A: INSERT INTO canada_contacts (first_name, last_name, email) SELECT first_name, last_name, email FROM customer WHERE country = 'Canada';
Q: What happens if you run DELETE FROM invoice; without a WHERE clause?
A: Every row in the invoice table is deleted. The table itself still exists (unlike DROP TABLE), but it is now empty.
Q: Write a single INSERT statement that adds three new rows to a referral table with columns first_name, last_name, and email.
A: INSERT INTO referral (first_name, last_name, email) VALUES ('Alice', 'Smith', 'a.smith@email.com'), ('Bob', 'Jones', 'b.jones@email.com'), ('Carol', 'Lee', 'c.lee@email.com');
INSERT, UPDATE, and DELETE are constrained by everything covered in Part 2 (primary keys, foreign keys, NOT NULL, UNIQUE, CHECK). The WHERE clause used in UPDATE and DELETE is the same WHERE covered in Part 1. Aggregate functions from Part 3 can be used inside INSERT ... SELECT to build summary tables. In later units, these operations extend to multi-table scenarios involving JOINs, subqueries, and transactions.
INSERT INTO, INSERT VALUES, INSERT SELECT, multi-row INSERT, RETURNING clause, UPDATE SET, UPDATE WHERE, DELETE FROM, DELETE WHERE, CRUD operations, data manipulation language, DML, SERIAL insert, auto-increment insert, foreign key delete order, bulk insert, point-in-time snapshot, safe deletion, PostgreSQL RETURNING, RDB 101, Sophia Pathways, relational database fundamentals