Inserting, Updating & Deleting Data

INSERT INTO table (columns) VALUES (...) adds rows — several at once if you separate them with commas. Columns you leave out get their default value. RETURNING shows the stored rows, including generated ids.

UPDATE ... SET ... WHERE ... changes existing rows; DELETE FROM ... WHERE ... removes them. The WHERE clause decides which rows are affected.

Forgetting WHERE updates or deletes every row in the table. Write the WHERE first, test it with a SELECT, and use transactions (covered in Intermediate) for important changes.

data.sqlSQL

INSERT INTO students (full_name, form, gender, birth_date, email, fee_balance)
VALUES
    ('Amina Hassan',  4, 'F', '2008-03-14', 'amina@example.com',  0),
    ('Baraka Mushi',  3, 'M', '2009-07-02', NULL,                 150000),
    ('Neema Kimaro',  4, 'F', '2008-11-21', 'neema@example.com',  50000),
    ('Juma Said',     2, 'M', '2010-01-30', 'juma@example.com',   200000),
    ('Rehema Mollel', 1, 'F', '2011-05-09', NULL,                 300000)
RETURNING id, full_name, created_at;

UPDATE students
SET fee_balance = fee_balance - 50000
WHERE full_name = 'Neema Kimaro'
RETURNING full_name, fee_balance;

UPDATE students SET phone = '0712345678' WHERE id = 1;

INSERT INTO students (full_name, form, gender) VALUES ('Test Student', 1, 'M');
DELETE FROM students WHERE full_name = 'Test Student';

SELECT id, full_name, form, fee_balance, phone FROM students ORDER BY id;

Runs in your browser · PostgreSQL

Key points

Insert many rows in one statement; RETURNING shows what was stored.
UPDATE and DELETE affect every row that matches WHERE — or every row if it's missing.
Check your WHERE with a SELECT before running UPDATE or DELETE.

Exercise

Insert three teachers into your teachers table. Give one of them a 10% salary increase with UPDATE (salary = salary * 1.10), mark another as inactive, and delete a teacher by id. Use RETURNING each time.

Show solution

Try the exercise yourself first — then compare your approach with this one.

RETURNING shows exactly what each statement stored or changed. Every UPDATE and DELETE has a WHERE clause that targets one teacher — without it, every row would change.

teachers-data.sqlSQL

INSERT INTO teachers (full_name, subject, phone, hired_on, salary) VALUES
    ('Mr. Mwakyusa', 'Mathematics', '0713000001', '2015-01-12', 1200000),
    ('Ms. Lyimo',    'Biology',     '0754000002', '2019-07-01', 1100000),
    ('Mrs. Mrema',   'English',     '0688000003', '2021-01-04', 1050000)
RETURNING id, full_name, salary;

UPDATE teachers SET salary = salary * 1.10
WHERE full_name = 'Ms. Lyimo'
RETURNING full_name, salary;          -- 1210000.00

UPDATE teachers SET is_active = false
WHERE full_name = 'Mrs. Mrema'
RETURNING full_name, is_active;

DELETE FROM teachers WHERE id = 1
RETURNING id, full_name;

SELECT id, full_name, salary, is_active FROM teachers ORDER BY id;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding