← QCE Digital Solutions Unit 2

Update anomaly

A problem where a repeated fact is changed in one row but not its other copies, so the data disagrees. Caused by redundancy, and fixed by normalisation.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 45

Understanding Update anomaly in QCE Digital Solutions Unit 2

What it does: Ava's address is repeated in three rows. Changing it in one row but missing the others would leave the data inconsistent. The same person now has two addresses. The change reached one row but not the other, so the data disagrees. An update anomaly leaves the data disagreeing with itself. Being unable to add a row is an insertion anomaly. Repeating a fact on many rows is redundancy. Every copy must be changed, and it is easy to miss one. If a fact is stored once, there is only one place to update. Redundancy creates the extra copies that can fall out of date.

Common mistakes: Filtering by orderId changes the address in only one row. Student 3's other orders keep the old address, which causes an update anomaly. It should be WHERE studentId = 3; so every copy changes. In SQL, UPDATE is followed directly by the table name. FROM is not allowed here. It should be UPDATE bookings so the address of every row for guest 4 can be changed.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Update anomaly to a class →

This module's key terms are also in a printable crossword (Modules 144–185) and word search (Modules 144–228).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A student's address appears in 3 rows of an Orders table. You change it in only 1 row. What is this called?
An insertion anomalyAn update anomaly
Matches
A student moves house. The office edits the address in their latest order row only, and leaves the older order rows alone.
↓
The address is repeated in several rows, and only one copy was changed. The old rows still show the old address, which is an update anomaly.
Applying
Meanings
Term
update anomaly
Meaning
A problem where a repeated fact is changed in one row but not its other copies, so the data disagrees. Caused by redundancy, and fixed by normalisation.
Practice
UPDATE products SET category = 'Writing' WHERE productId = 1;
Testing
Gaps
WHERE = 1;
Check Answer
Spots
3SET address = '9 Elm St'
4WHERE orderId = 3;

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Sea Turtle' badge — Scope Navigator, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A student's address appears in 3 rows of an Orders table. You change it in only 1 row. What is this called?
A. An insertion anomaly
B. An update anomaly ✓
The same fact is now stored two different ways. Changing one copy and missing the others is an update anomaly.
2. Which statement describes the result of an update anomaly?
A. A new row cannot be added without extra data
B. The same fact has different values in different rows ✓
An update anomaly leaves the data disagreeing with itself. Being unable to add a row is an insertion anomaly.
3. Which table design is MOST likely to cause an update anomaly?
A. One big table that repeats a customer's address on every order row ✓
B. Separate Customer and Order tables linked by a foreign key
Repeating a fact on many rows is redundancy. Every copy must be changed, and it is easy to miss one.
4. What is the root cause of an update anomaly?
A. Redundancy: the same fact is stored in more than one row ✓
B. Using a primary key on the table
If a fact is stored once, there is only one place to update. Redundancy creates the extra copies that can fall out of date.
5. Ava moves house. Her address is stored in rows 2, 5 and 9. After the update only row 2 shows the new address. What is the problem?
A. Rows 5 and 9 still hold the old address, so the data is inconsistent ✓
B. Row 2 is now wrong and rows 5 and 9 are correct
The updated row is correct. The copies that were missed are out of date, so the table disagrees with itself.

Match the Pairs

UPDATE students SET address = '5 Park St' WHERE studentId = 3; → Changes the address in only one row. If the same address is repeated in other rows, those copies stay old and the data disagrees (an update anomaly).
UPDATE students SET address = '5 Park St' WHERE address = '12 Hill Rd'; → Changes every row that holds the old address, so all copies of the repeated fact stay in agreement and no update anomaly is left behind.
Redundancy causes update anomalies because the same address is stored in many rows, so each copy must be changed separately. → Orders: (1, Ava, 12 Hill Rd), (2, Ava, 12 Hill Rd), (3, Ava, 12 Hill Rd)
An update anomaly shows up when one customer has two different addresses after a change was made in only one row. → Row 1: Ava, 5 Park St | Row 2: Ava, 12 Hill Rd
A student moves house. The office edits the address in their latest order row only, and leaves the older order rows alone. → The address is repeated in several rows, and only one copy was changed. The old rows still show the old address, which is an update anomaly.
Fix: keep the address in every order row and just try to remember to update all of them each time. → Relying on memory to update every copy does not remove the redundancy that causes the anomaly. Mistakes will still happen.

Key Term

update anomaly
A problem where a repeated fact is changed in one row but not its other copies, so the data disagrees. Caused by redundancy, and fixed by normalisation.

Fill in the Blanks

1. UPDATE Orders SET address = '5 Elm St' WHERE orderId = 1;
Hint: Type the column that picks the one row to change, which leaves the other copies unchanged.
2. UPDATE Orders SET address = '5 Elm St' WHERE orderId = 1;
Hint: Type the column holding the repeated fact that is being changed.
3. SELECT DISTINCT address FROM Orders WHERE custId = 3;
Hint: Type the keyword that removes duplicate results, so two different addresses show the data disagrees.
4. UPDATE Customers SET address = '5 Elm St' WHERE custId = 3;
Hint: Type the table where each address is stored only once after normalising, so one update is enough.
5. SELECT COUNT(*) FROM Orders WHERE custId = 3;
Hint: Type the aggregate function that counts rows, showing how many redundant copies of a fact need updating.

Spot the Error

Question 1
A -- Address repeated per order
B UPDATE orders
C SET address = '9 Elm St'
D WHERE orderId = 3;
Line D — Filtering by orderId changes the address in only one row. Student 3's other orders keep the old address, which causes an update anomaly. It should be WHERE studentId = 3; so every copy changes.
Question 2
A UPDATE FROM bookings
B SET address = '7 Bay Rd'
C WHERE guestId = 4;
Line A — In SQL, UPDATE is followed directly by the table name. FROM is not allowed here. It should be UPDATE bookings so the address of every row for guest 4 can be changed.
Question 3
A newAddr <- '5 Pine St'
B for each row in orders
C if row.studentId = 3 then
D newAddr <- row.address
Line D — The assignment goes the wrong way, so the old address overwrites the new one and no stored copy changes. It should be row.address <- newAddr, applied to every copy of the repeated address.
Question 4
A SELECT DISTINCT address
B FROM address
C WHERE studentId = 3;
Line B — FROM must name a table, and address is a column. It should be FROM orders. If this query returns more than one address for one student, the copies disagree, which is an update anomaly.
Question 5
A count <- 0
B for each row in orders
C if row.addr = newAddr then
D count <- count + 1
E print count
Line C — This counts the rows that already have the new address. To find the stale copies left behind after a partial update, compare with != so it counts rows that still differ.

Student with a class code? Join your class →