SQL INSERT, UPDATE and DELETE: Changing Data in a Database

SELECT reads data from a database; three other SQL statements change it. INSERT INTO adds a new record, UPDATE modifies existing records, and DELETE FROM removes them. All three are examined at GCSE, and all three depend on getting the WHERE clause right.

The example table

Every query below runs against a table called Student:

StudentID is the primary key: unique for every record, and the safest way to target a single row.

How to add a record with INSERT INTO

Syntax:

INSERT INTO tableName (column1, column2, column3)
VALUES (value1, value2, value3);

Example — add a new Year 7 student:

INSERT INTO Student (StudentID, FirstName, Surname, YearGroup, HousePoints)
VALUES (5, 'Rosa', 'Almeida', 7, 0);

Three rules to remember:

• Text values go in quotation marks; numbers do not. 'Rosa' is quoted, 7 is not.
• The values must appear in the same order as the columns you listed. Swap two and the data lands in the wrong fields, often without any error at all if the data types happen to match.
• Naming the columns is optional but strongly advised. You can write INSERT INTO Student VALUES (5, 'Rosa', ...) and rely on the table's own column order, but that breaks silently if the table is ever altered.

If a column has a default value or allows nulls, you may omit it from the list entirely.

How to change a record with UPDATE

Syntax:

UPDATE tableName
SET column1 = newValue
WHERE condition;

Example — give Jack ten more house points:

UPDATE Student
SET HousePoints = HousePoints + 10
WHERE StudentID = 2;

Notice that the new value can be calculated from the old one. HousePoints = HousePoints + 10 reads the current value and adds to it.

Example — change several fields at once, separating the assignments with commas:

UPDATE Student
SET YearGroup = 9, HousePoints = 0
WHERE YearGroup = 8;

That statement moves every Year 8 student up to Year 9 and resets their points — two students in our table, in a single operation.

How to remove a record with DELETE FROM

Syntax:

DELETE FROM tableName
WHERE condition;

Example — remove a student who has left:

DELETE FROM Student
WHERE StudentID = 3;

DELETE removes whole rows. There is no way to delete a single field with it — to empty a field you would use UPDATE ... SET column = NULL.

The WHERE clause is not optional in practice

This is the single most important thing to understand about UPDATE and DELETE.

If you omit the WHERE clause, the statement applies to every row in the table:

DELETE FROM Student;      -- deletes every student
UPDATE Student SET YearGroup = 11;   -- puts everyone in Year 11

Neither statement is an error. The database will carry out exactly what you asked. In a real system with no recent backup, that is a genuine disaster, which is why professionals often write the WHERE clause first and the rest of the statement afterwards.

In an exam, an UPDATE or DELETE answer without a WHERE clause will almost always lose the mark, because the mark scheme expects the change to be targeted.

Useful conditions in a WHERE clause

Checking your work before and after

A habit worth building, and worth mentioning in a longer exam answer: run a SELECT with the same WHERE clause first.

SELECT * FROM Student WHERE YearGroup = 8;

If that returns exactly the rows you intended to change, the WHERE clause is correct, and you can safely reuse it in the UPDATE or DELETE. Run the same SELECT afterwards to confirm the change did what you expected.

Common mistakes to avoid

• Omitting WHERE, and changing or deleting the entire table.
• Quoting numbers or leaving text unquoted. WHERE StudentID = '2' may still work in some systems but is not correct practice; WHERE Surname = Kelly will fail.
• Using = to compare against a null. Nulls need IS NULL, not = NULL.
• Forgetting the semicolon at the end of the statement.
• Mismatching the column list and the value list in INSERT INTO — a different number of items is an error, a different order often is not.
• Trying to delete a record another table depends on. If a foreign key references it, the database will normally refuse.

Frequently asked questions

What happens if I run UPDATE or DELETE without a WHERE clause?

The statement affects every row in the table. DELETE FROM Student; empties the table entirely; UPDATE Student SET YearGroup = 11; puts every student into Year 11. The database does not warn you, because you have written a perfectly valid instruction. Unless the operation is inside a transaction that can be rolled back, the only recovery is from a backup — which is exactly why regular backups exist.

Can I insert a record without giving a value for every column?

Yes, provided the columns you omit either allow nulls or have a default value defined. List only the columns you are supplying, and give matching values. A column defined as NOT NULL with no default must always be supplied, and the primary key must always be present and unique — attempting to insert a duplicate primary key will be rejected.

What is the difference between DELETE and dropping a table?

DELETE FROM Student; removes the rows but leaves the table structure — the columns, data types and constraints — in place, ready to receive new records. DROP TABLE Student; removes the table itself, structure and all. Deleting rows is a data operation; dropping a table is a structural one, and it is far harder to undo.

Why can't I delete a record that another table refers to?

Because of referential integrity. If a Grades table holds rows referencing StudentID = 3 as a foreign key, deleting student 3 would leave those grade records pointing at nothing — an orphaned reference. The database enforces the relationship by refusing the delete. To proceed you must either remove the dependent records first or configure the relationship to cascade the deletion, which removes them automatically.

For Socratic GCSE Computer Science tutoring — databases, SQL and beyond — visit aitutors.me.