SQL: Modifying Data with INSERT, UPDATE and DELETE
Modifying a Database
So far you have learnt to retrieve data with SELECT. Real databases also need to grow and change: new students join a school and need rows adding; contact details change and need updating; leavers’ records are eventually removed. SQL provides three commands for these operations: INSERT INTO, UPDATE, and DELETE FROM. We use the same Students, Courses and Enrolments tables as before throughout all the examples in this lesson.
Reminder of the schema:
Students — student_id (PK), student_name, year_group
Courses — course_id (PK), course_title
Enrolments — enrolment_id (PK), student_id (FK), course_id (FK)
INSERT INTO — Adding New Rows
To add a new record, use:
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);
The columns list and the values list must match in number and order. Text values go in single quotes; integers do not. You do not have to include every column — you can omit columns that have a default value or are auto-generated by the database (such as an auto-increment primary key).
Task: Add a new student, Dani Osei, in year group 10, with student_id 4.
INSERT INTO Students (student_id, student_name, year_group)
VALUES (4, ‘Dani Osei’, 10);
After this query runs, the Students table gains a new row: 4 | Dani Osei | 10. Notice that 4 (an integer) has no quotes, while ‘Dani Osei’ (text) does.
Adding an enrolment record: To enrol Dani (student_id = 4) on course 2:
INSERT INTO Enrolments (enrolment_id, student_id, course_id)
VALUES (10, 4, 2);
All three values are integers so none need quotes. The foreign key value (student_id = 4) must already exist in the Students table — you cannot enrol a student who doesn’t exist.
UPDATE — Changing Existing Data
To change data in existing rows, use:
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
The SET clause names the columns to change and their new values. The WHERE clause identifies which rows to update. You can update more than one column at once by separating them with commas in the SET clause.
Task: Change Aisha Khan’s year group to 11.
UPDATE Students
SET year_group = 11
WHERE student_name = ‘Aisha Khan’;
Only the row (or rows) where student_name equals ‘Aisha Khan’ is changed. All other rows remain exactly as they were.
Updating two columns at once: Rename student_id 2 and move them to year 11.
UPDATE Students
SET student_name = ‘Benjamin Okafor’, year_group = 11
WHERE student_id = 2;
Two columns are updated in a single query. Using student_id in the WHERE clause is safer than using student_name, because student_id is the primary key and uniquely identifies one row.
Critical rule: An UPDATE without a WHERE clause changes every single row in the table. Writing UPDATE Students SET year_group = 11; would change all students’ year groups to 11, overwriting all existing data. Always include a WHERE clause to target only the intended rows.
DELETE FROM — Removing Rows
To remove one or more rows from a table, use:
DELETE FROM table_name
WHERE condition;
The WHERE clause identifies which rows to remove. Every row matching the condition is permanently deleted — this cannot be undone in a basic SQL query. If no WHERE clause is given, every row in the table is deleted.
Task: Remove the student with student_id 3 from the Students table.
DELETE FROM Students
WHERE student_id = 3;
Only the row where student_id = 3 is deleted. Using the primary key in the WHERE clause ensures exactly one row is targeted.
Deleting an enrolment: Remove all enrolments for student_id 1.
DELETE FROM Enrolments
WHERE student_id = 1;
If student 1 has three enrolment records, all three are deleted by this single query because all three rows satisfy the condition.
The WHERE clause on DELETE is never optional in practice. DELETE FROM Students; without a WHERE clause deletes every student record in the table — all data is lost. Always test a WHERE condition with a SELECT query first to confirm it targets the right rows, before running DELETE.
Test Before You Modify
A good habit before running any UPDATE or DELETE on a real database is to first run a SELECT query with the same WHERE condition. If the SELECT returns exactly the rows you expected, you can be confident the UPDATE or DELETE will target the right records and no others. For example, before running DELETE FROM Students WHERE year_group = 10; first run SELECT * FROM Students WHERE year_group = 10; to review exactly which rows will be affected. This test-first approach is a practical way to prevent accidental data loss.
Comparing the Three Commands
To summarise the three data-modification commands:
- INSERT INTO — adds one new row. Needs column names and matching values. Cannot target existing rows.
- UPDATE…SET…WHERE — changes values in existing rows. Must use WHERE to target specific rows. Can update multiple columns in one query.
- DELETE FROM…WHERE — removes whole rows. Must use WHERE to target specific rows. The deletion is immediate and permanent.