SQL Updates

IB Syllabus: A3.3.3: Explain how SQL can be used to update data in a database (INSERT INTO, UPDATE SET, DELETE), including the effect of indexed columns on update performance.

---

Key Concepts

Three DML Commands That Change Data

Once a database exists and contains data, the day-to-day work falls to three Data Manipulation Language (DML) commands:

Command	What it does
INSERT INTO	Adds one or more new rows to a table.
UPDATE ... SET ...	Changes the values in existing rows.
DELETE FROM	Removes existing rows from a table.

SELECT (covered in SQL Queries) is the fourth DML command, it reads data without changing it. The three on this page actually change what is stored.

A defining property of all three: they take effect at the row level, and they use a WHERE clause to decide which rows are affected. The WHERE clause is the single most important part of any update, forget it, and the operation applies to every row in the table. That mistake has wrecked many production databases.

INSERT INTO

INSERT INTO adds a new row. There are two common forms.

Explicit column list (recommended):

INSERT INTO Member (MemberID, FirstName, Surname, DateOfBirth, Tier)
VALUES (1005, 'Lila', 'Okafor', '1989-02-11', 'Premium');

The column list says which columns the values map to. This form survives schema changes, adding a new column does not break existing INSERT statements (as long as the new column has a default or allows NULL).

Positional form (concise but fragile):

INSERT INTO Member
VALUES (1005, 'Lila', 'Okafor', '1989-02-11', 'Premium');

You must provide a value for every column in the exact declaration order. If anyone adds a column to the table, this statement breaks immediately. Use the explicit form in real code.

Inserting many rows at once:

INSERT INTO Member (MemberID, FirstName, Surname, DateOfBirth, Tier) VALUES
  (1006, 'Daniel', 'Park',     '1978-09-04', 'Standard'),
  (1007, 'Hiroshi', 'Tanaka',  '1992-06-21', 'Premium'),
  (1008, 'Mei',    'Wong',     '2001-11-17', 'Standard');

Inserting from another query:

INSERT INTO Member_Archive (MemberID, FirstName, Surname)
SELECT MemberID, FirstName, Surname
FROM Member
WHERE Tier = 'Standard' AND LastBookedOn < '2024-01-01';

This copies inactive standard-tier members into an archive table in one statement.

What INSERT can reject:

• A primary-key value that already exists (uniqueness violation).
• A foreign-key value that does not exist in the referenced table (referential integrity violation).
• A value that does not match the column’s declared type or constraints (data integrity violation).
• A NULL in a NOT NULL column without a default.

Each of these failures is a feature, not a bug, the database refuses to let bad data in.

UPDATE … SET

UPDATE changes existing rows. The structure is:

UPDATE table
SET    column1 = value1,
       column2 = value2
WHERE  condition;

Updating one row:

UPDATE Member
SET    Tier = 'Premium'
WHERE  MemberID = 1001;

Updating many rows that match a condition:

UPDATE Member
SET    Tier = 'Premium'
WHERE  DateOfBirth < '1980-01-01' AND Tier = 'Standard';

Updating a column using its current value:

UPDATE Class
SET    Capacity = Capacity + 5
WHERE  Title LIKE '%Sunrise%';

The crucial WHERE clause. Without WHERE, UPDATE Member SET Tier = 'Premium' makes every member premium. Exams rarely test this directly, but the safety habit matters; always type the WHERE first when constructing an UPDATE.

What UPDATE can reject: the same constraints that protect INSERT also protect UPDATE, you cannot update a value into something that violates the column’s type, a unique constraint, or a foreign-key reference.

DELETE FROM

DELETE FROM removes rows. Like UPDATE, it operates on rows matching the WHERE clause:

DELETE FROM Booking
WHERE  Status = 'cancelled' AND BookedOn < '2024-01-01';

Deleting everything in a table:

DELETE FROM Booking;     -- removes every row, table still exists

This is occasionally what you want, but it is also the single most common “oh no” moment in the history of database administration. Tools like mysql will happily run it without confirmation.

For wiping a whole table cleanly, the related DDL command TRUNCATE TABLE Booking; is usually faster (it does not log every row deletion) but bypasses some DBMS features like cascading actions and triggers.

Referential-integrity protection. If Booking.MemberID has a FOREIGN KEY ... REFERENCES Member(MemberID), deleting a member that has bookings either:

• Fails (ON DELETE RESTRICT, the default in most DBMSs).
• Cascades (ON DELETE CASCADE): the bookings are deleted automatically along with the member.
• Sets the FK to NULL (ON DELETE SET NULL).

The DBA chooses the right behaviour per foreign-key constraint. See Data Types and Keys for the full discussion.

How Indexes Affect Updates

One subtle but important consequence of having indexes is the effect of indexed columns on update performance. The key trade-off is:

• Indexes speed up SELECTs. An index on Surname lets the DBMS find members with a particular surname in O(log n) instead of scanning every row.
• Indexes slow down INSERTs, UPDATEs, and DELETEs. Every write must also update each index on the table. A table with eight indexes pays roughly eight times the per-row overhead of an unindexed table during writes.

Concrete consequences:

Workload	Indexing strategy
Read-heavy (reporting, dashboards)	Add indexes on every column commonly used in WHERE, JOIN, or ORDER BY.
Write-heavy (logging, high-throughput inserts)	Keep indexes to the minimum that the most important queries need.
Mixed	Profile actual workload; remove indexes that are not used in any query plan.

Updating an indexed column specifically is the most expensive case, because the old index entry must be removed and a new one inserted. Updating an unindexed column only rewrites the row itself.

Mark-scheme angle: when a question asks “explain why INSERTs may be slower on a table with many indexes,” the expected points are:

1. Every index is a separate data structure that must stay in sync with the table.
2. Each INSERT must add an entry to every index, not just to the table.
3. The cost scales with the number of indexes and their size.
4. Therefore: indexes are a trade-off, not free; pick them deliberately.

Update Anomalies Without Foreign Keys

The most common practical failure during updates is forgetting to maintain referential integrity manually when the schema does not enforce it. Suppose the developers used a denormalised BookingLog table that stores the member’s name on every row:

BookingLog(BookingID, MemberName, ClassTitle, BookedOn)

When Aroha legally changes her surname, the update is no longer a simple UPDATE Member SET Surname = ..., it must touch the BookingLog too:

UPDATE Member
SET    Surname = 'Te Whetu'
WHERE  MemberID = 1001;

UPDATE BookingLog
SET    MemberName = 'Aroha Te Whetu'
WHERE  MemberName = 'Aroha Kahurangi';

If the second statement is missed, the data disagrees with itself. This is precisely the update anomaly that 3NF normalisation eliminates, but the moment you denormalise, you take responsibility for maintaining consistency manually.

Transactions: A Preview

Multiple INSERT, UPDATE, and DELETE statements that must succeed or fail together should be wrapped in a transaction (BEGIN TRANSACTION ... COMMIT; or ROLLBACK;). The HL page on Transactions and Views covers this in detail. For SL, the key idea is: a transaction is the database’s mechanism for “all of these changes, or none of them.”

---

Worked Examples

Example 1: Adding a New Member

A new member, Lila Okafor, joins the Harbour Run club. Her ID is 1005, she was born on 11 February 1989, and she signs up at the Premium tier.

INSERT INTO Member (MemberID, FirstName, Surname, DateOfBirth, Tier)
VALUES (1005, 'Lila', 'Okafor', '1989-02-11', 'Premium');

After this runs, a SELECT confirms the new row:

SELECT * FROM Member WHERE MemberID = 1005;
-- 1005 | Lila | Okafor | 1989-02-11 | Premium

Example 2: Upgrading a Group of Members

The club runs a promotion: every Standard-tier member who has booked five or more classes in 2026 is upgraded to Premium. Assume a query first identifies the relevant members; the update statement is:

UPDATE Member
SET    Tier = 'Premium'
WHERE  Tier = 'Standard'
  AND  MemberID IN (
        SELECT MemberID
        FROM   Booking
        WHERE  BookedOn BETWEEN '2026-01-01' AND '2026-12-31'
        GROUP BY MemberID
        HAVING COUNT(*) >= 5
       );

(A subquery is beyond what this page covers, but the mechanics of UPDATE ... SET ... WHERE are exactly what A3.3.3 expects.)

Example 3: Removing Cancelled Bookings

The club archives or removes cancelled bookings older than two years. The cleanup statement:

DELETE FROM Booking
WHERE  Status = 'cancelled'
  AND  BookedOn < '2024-05-22';

This affects only rows matching both conditions; other bookings stay untouched.

Example 4: Indexed-Column Update Cost

Suppose Member has three indexes: on MemberID (the primary-key index, automatic), on Surname, and on (Tier, DateOfBirth).

Statement	Index maintenance cost
UPDATE Member SET FirstName = 'Aroha' WHERE MemberID = 1001;	Zero index updates beyond the row itself. FirstName is not indexed.
UPDATE Member SET Surname = 'Te Whetu' WHERE MemberID = 1001;	Must remove the old Surname index entry and insert the new one.
UPDATE Member SET Tier = 'Premium', DateOfBirth = '1989-02-11' WHERE MemberID = 1005;	Must rewrite the composite (Tier, DateOfBirth) index entry, both indexed columns are changing.

The principle: the cost of an UPDATE depends on which columns change, not just how many rows. Updating an unindexed column is much cheaper than updating an indexed one.

---

Quick Check

Q1. Which SQL command adds a new row to a table?

Q2. What happens if you run UPDATE Member SET Tier = 'Premium'; with no WHERE clause?

Q3. Which of the following causes the DBMS to reject an INSERT statement?

Q4. A table has many indexes. How does this affect INSERT, UPDATE, and DELETE performance?

Q5. Booking.MemberID is a foreign key to Member.MemberID with ON DELETE CASCADE. What happens when you run DELETE FROM Member WHERE MemberID = 1001; and there are 5 bookings for member 1001?

---

Trace the Statement

Start from the sample Member table:

Member

MemberID	FirstName	Surname	DateOfBirth	Tier
1001	Aroha	Kahurangi	2008-03-14	Standard
1002	Marlon	Tan	1995-07-22	Premium
1003	Sebastián	Quispe	1980-11-02	Standard
1004	Priya	Nair	2003-05-30	Premium

For each statement, predict how many rows in the table are changed (inserted, updated, or deleted).

Type the answer as a single integer.

Statement	Rows changed
INSERT INTO Member VALUES (1005, 'Lila', 'Okafor', '1989-02-11', 'Premium');
UPDATE Member SET Tier = 'Premium' WHERE Tier = 'Standard';
UPDATE Member SET Surname = 'Te Whetu' WHERE MemberID = 1001;
DELETE FROM Member WHERE DateOfBirth < '1990-01-01';
UPDATE Member SET Tier = 'Premium' WHERE 1=0;

---

Fill in the Blanks

Complete the three DML statements.

-- Add a new booking
 Booking (BookingID, MemberID, ClassID, BookedOn, Status)
VALUES ('B9005', 1003, 'C402', '2026-05-31', 'confirmed');

-- Change every Standard member born before 1990 to Premium
 Member
 Tier = 'Premium'
WHERE Tier = 'Standard' AND DateOfBirth < '1990-01-01';

-- Remove cancelled bookings older than the start of 2024
DELETE  Booking
WHERE Status = 'cancelled' AND BookedOn < '2024-01-01';

---

Spot the Error

A developer is trying to upgrade a single member to Premium tier. One line has an error. Click the line with the error, then choose the correct fix.

1-- Upgrade member 1001 to Premium tier 2UPDATE Member Tier = 'Premium' 3WHERE MemberID = 1001;

Pick the correct fix for line 2:

---

Predict the Result

Starting from the sample Member table above, how many rows are changed by:

UPDATE Member SET Tier = 'Premium' WHERE Tier = 'Standard';

Type the answer as a single integer.

Starting from the sample Member table above, how many rows are deleted by:

DELETE FROM Member WHERE DateOfBirth < '1990-01-01';

Type the answer as a single integer.

---

Practice Exercises

Core

1. [Core] SQL: DML [3 marks] State the three SQL DML commands used to modify data, and describe in one short sentence what each one does.

2. [Core] SQL: DML [3 marks] Write an SQL statement that adds a new class to the Harbour Run Class table:
   • ClassID = 'C404', Title = 'Evening Trail', Coach = 'Daniel Park', StartsAt = '2026-07-01 18:30', Capacity = 25.

3. [Core] SQL: DML [3 marks] Write an SQL statement that changes the status of every booking made before 1 January 2024 to 'archived'.

4. [Core] SQL: DML [3 marks] Write an SQL statement that removes every cancelled booking older than two years (assume today is 2026-05-22).

Extension

1. [Extension] SQL: DML [3 marks] Explain what would happen if a developer ran DELETE FROM Booking; without a WHERE clause, and outline two practices that protect against accidental mass deletes in real systems.

2. [Extension] SQL: DML [4 marks] A Member table has indexes on MemberID (primary key) and Surname. Explain which of the following two updates is faster, and why:

   • (a) UPDATE Member SET FirstName = 'Aroha' WHERE MemberID = 1001;
   • (b) UPDATE Member SET Surname = 'Te Whetu' WHERE MemberID = 1001;

Challenge

1. [Challenge] SQL: DML [5 marks] The Booking.MemberID column is a foreign key to Member.MemberID. Explain what happens in each of the following cases, assuming the foreign key has ON DELETE RESTRICT:
   • (a) Inserting a booking whose MemberID does not exist in Member.
   • (b) Deleting a member who has three existing bookings.
   • (c) Updating a member’s MemberID from 1001 to 9999.
2. [Challenge] SQL: DML [6 marks] Discuss the trade-off between adding many indexes to a table and keeping write performance fast. Refer to read-heavy and write-heavy workloads, and give an example of a workload where each strategy is appropriate.

Exam-Style

1. [Exam-Style] SQL: DML [6 marks] For the Harbour Run schema, construct SQL statements that perform each of the following operations. Use one statement per task. (2 marks each)
   • (a) Add a new member with MemberID = 1099, the rest of the details fictional but valid.
   • (b) Change the surname of member 1002 to 'Tan-Lopez'.
   • (c) Delete every booking with Status = 'no-show' from May 2024.

Note for IB CS learners: A3.3.3 (SQL DML writing) is new in the 2027 syllabus. Expect direct “construct an INSERT/UPDATE/DELETE statement” questions worth 2-4 marks each. Mark schemes will almost certainly mirror A3.3.2’s pattern: 1 mark for the right command, 1 mark for the table and column list, 1 mark for the WHERE clause. The new emphasis on indexed-column performance is also new, be ready to explain why indexes slow down writes.

---

Connections

• Previous: SQL Queries. SELECT is the read half of DML; INSERT/UPDATE/DELETE are the write half.
• Related: Data Types and Keys. Referential integrity rules (CASCADE, RESTRICT, SET NULL) determine what happens when an INSERT, UPDATE, or DELETE crosses a foreign key.
• Related: Normalisation. A well-normalised schema means each fact lives in one place, so updates touch one row in one table.
• Next (HL): Transactions and Views. Transactions group multiple INSERT/UPDATE/DELETE statements into an all-or-nothing unit, preserving integrity during multi-step operations.