SQL — modifying data with INSERT, UPDATE, DELETE
SELECT only reads data. To change the contents of a database, use INSERT, UPDATE and DELETE. These are sometimes called DML (Data Manipulation Language) statements.
INSERT — add a new row
INSERT INTO Students (StudentID, Name, DOB, TutorGroup)
VALUES (1004, 'Daniel', '2009-04-08', '10C');
You list the columns and the corresponding values. The order matters — values match the column list.
If you provide values for every column in table order, you can omit the column list:
INSERT INTO Students VALUES (1004, 'Daniel', '2009-04-08', '10C');
But it's safer to list the columns explicitly — it survives column reordering.
Multi-row insert (extension)
INSERT INTO Subjects (SubjectID, Name) VALUES
('S04', 'Geography'),
('S05', 'History');
UPDATE — change existing rows
UPDATE Students
SET TutorGroup = '11A'
WHERE StudentID = 1004;
- SET — which columns to change and to what value.
- WHERE — which rows are affected.
You can change multiple columns at once:
UPDATE Students
SET TutorGroup = '11A', YearGroup = 11
WHERE TutorGroup = '10A';
Critical: forgetting the WHERE clause updates every row in the table. Test with a SELECT first.
DELETE — remove rows
DELETE FROM Students
WHERE StudentID = 1004;
- DELETE FROM table — choose the table.
- WHERE — which rows to remove.
Like UPDATE, missing WHERE deletes everything in the table:
DELETE FROM Students; -- removes ALL rows. Be very careful.
To delete the entire table including its structure, use DROP TABLE Students; (DDL, not DML).
Effect on referential integrity
Suppose Enrolments has StudentID as a foreign key to Students. If you try:
DELETE FROM Students WHERE StudentID = 1001;
The DBMS may refuse if 1001 has rows in Enrolments — this would leave orphan foreign keys. Behaviour depends on rule:
- RESTRICT (default) — refuse to delete.
- CASCADE — also delete the dependent rows in Enrolments.
- SET NULL — set the foreign key to NULL.
Choose the rule when defining the relationship, not at delete time.
✦Worked example— Worked example — manage a library
-- Add a new book
INSERT INTO Books (BookID, Title, Author, Year)
VALUES (101, 'Project Hail Mary', 'Andy Weir', 2021);
-- Mark the book as on loan
UPDATE Books
SET Available = 0
WHERE BookID = 101;
-- Remove a deleted/destroyed book
DELETE FROM Books
WHERE BookID = 5;
Best practices
- Always include WHERE in UPDATE and DELETE — even when "obvious".
- Test the WHERE first with SELECT — make sure it picks the rows you intend.
- Use transactions to wrap related changes (BEGIN / COMMIT / ROLLBACK).
- Backups before any large change.
- Audit logs for who changed what.
Common pattern — soft delete
Instead of DELETE, set a deleted = TRUE flag. Lets you recover and preserves audit trail.
UPDATE Students SET Deleted = 1 WHERE StudentID = 1004;
⚠Common mistakes— Pitfalls
- UPDATE without WHERE — updates every row.
- DELETE without WHERE — deletes every row.
- Mismatched columns/values in INSERT. Order and count must match.
- Inserting values that violate constraints (e.g. duplicate primary key) — INSERT fails.
- Forgetting referential integrity. A DELETE may be refused or cascade unexpectedly.
✦Worked example— Worked example — orphan prevention
If a student has enrolments and you try DELETE FROM Students WHERE StudentID = 1001;:
- With RESTRICT, the DBMS refuses — fix by deleting their enrolments first.
- With CASCADE, both go in one operation — but check this is what you really want.
- Without referential integrity rules, you get orphan rows in Enrolments — broken database.
➜Try this— Quick check
State the result of each, given Students has rows 1001-1003:
INSERT INTO Students (StudentID, Name) VALUES (1004, 'Dan');
→ Adds Dan as 1004.
UPDATE Students SET Name = 'Daniel' WHERE StudentID = 1004;
→ Renames Dan to Daniel.
DELETE FROM Students WHERE Name = 'Daniel';
→ Removes Daniel's row, leaving 1001-1003.
AI-generated · claude-opus-4-7 · v3-deep-computer-science