Delete the Wrong Row: A Cheap Answer That Passes the Test

A cheap answer to the multiple table delete puzzle can delete the wrong row. This one reads only 6 pages and passes the check from the puzzle. Your job is to find the row it removes by mistake. A script that can delete the wrong row is worse than a slow one.

The Puzzle Again

The puzzle is in Delete Qualified Rows From Multiple Tables: A SQL Puzzle. The English class is cancelled. A student whose only class was English is deleted, together with the enrollment. A student with other classes stays, and only the English enrollment goes. A student who never took the class is not touched.

The script below creates a database named StudentClassFollowUpDemo. It holds the same three tables and a procedure that puts the data back.

IF DB_ID(N'StudentClassFollowUpDemo') IS NULL CREATE DATABASE StudentClassFollowUpDemo;
GO
USE StudentClassFollowUpDemo;
GO
DROP TABLE IF EXISTS dbo.StudentClass, dbo.Class, dbo.Student;
CREATE TABLE dbo.Student (ID int, StudentName varchar(100));
CREATE TABLE dbo.Class (ID int, ClassName varchar(100));
CREATE TABLE dbo.StudentClass (ID int, ClassID int, StudentID int);
GO
CREATE OR ALTER PROCEDURE dbo.ResetPuzzle
AS
BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE dbo.StudentClass;
    TRUNCATE TABLE dbo.Class;
    TRUNCATE TABLE dbo.Student;
    INSERT INTO dbo.Student (ID, StudentName) VALUES (1, 'Mark'), (2, 'John'), (3, 'Thomas');
    INSERT INTO dbo.Class (ID, ClassName) VALUES (1, 'Maths'), (2, 'English'), (3, 'Science');
    INSERT INTO dbo.StudentClass (ID, ClassID, StudentID) VALUES (1, 1, 1), (2, 2, 2), (3, 1, 3), (4, 2, 1), (5, 2, 3), (6, 3, 1);
END;
GO
EXEC dbo.ResetPuzzle;

The Cheap Answer

This answer takes three steps. It reads the class ID. It deletes the enrollments of that class. Then it deletes every student who has no enrollment left. The last step is one anti-join: a left join that keeps only the students with no match.

EXEC dbo.ResetPuzzle;

SET STATISTICS IO ON;

DECLARE @ClassID int = (SELECT ID FROM dbo.Class WHERE ClassName = 'English');

DELETE FROM dbo.StudentClass WHERE ClassID = @ClassID;

DELETE s
FROM dbo.Student AS s
LEFT JOIN dbo.StudentClass AS sc ON sc.StudentID = s.ID
WHERE sc.StudentID IS NULL;

SET STATISTICS IO OFF;

SELECT ID, StudentName FROM dbo.Student ORDER BY ID;

SELECT ID, ClassID, StudentID FROM dbo.StudentClass ORDER BY ID;

The total is 6 logical reads, against 19 for the starting script. John is gone, Mark and Thomas stay, and the three English enrollments are gone. The result matches the rules on this data. The answer looks right.

Find the Row It Gets Wrong

One student should stay, and this script deletes that student. It is not John, Mark or Thomas. Think about the rules before you read on. What kind of student does the last step not understand?

The Answer

The last step deletes every student with no enrollment. It cannot tell two kinds of student apart. One had English as the only class, so the cancellation left them empty. The other never had a class at all. The rules touch only the first kind.

Add a fourth student, Priya, who has not enrolled yet. Run the same script again.

EXEC dbo.ResetPuzzle;

INSERT INTO dbo.Student (ID, StudentName) VALUES (4, 'Priya');

SET STATISTICS IO ON;

DECLARE @ClassID int = (SELECT ID FROM dbo.Class WHERE ClassName = 'English');

DELETE FROM dbo.StudentClass WHERE ClassID = @ClassID;

DELETE s
FROM dbo.Student AS s
LEFT JOIN dbo.StudentClass AS sc ON sc.StudentID = s.ID
WHERE sc.StudentID IS NULL;

SET STATISTICS IO OFF;

SELECT ID, StudentName FROM dbo.Student ORDER BY ID;

Priya is gone. The English cancellation had nothing to do with Priya. The script deleted the wrong row, and it did so quietly, with no error and a low read count. The reads on this data are 7.

Why Cheap Answers Hide Bugs

A read count measures cost, not correctness. The test data had three students, and each of them had a class. The bug needs a student with none. A test that never contains such a student cannot find it. This is why the puzzle asks for a correct answer for any class name and any data. It is also why a low score deserves a second look before anyone trusts it.

Some versions of this answer also add the NOLOCK hint to the reads. The hint lets a statement read rows that are not committed yet. A delete should not decide from rows that can still roll back. On tables this small the hint does not matter, and it adds a risk.

The Fix

The fix is to decide from the enrollments, not from their absence. Group StudentClass by student, count the classes and flag the cancelled class. Delete only the students with one class and the flag set. Then delete the enrollments. The answer post Fewest Logical Reads: The Answer to the Delete Qualified Rows Puzzle explains the reasoning. The procedure is below.

CREATE OR ALTER PROCEDURE dbo.RemoveClass @ClassName varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @ClassID int = (SELECT ID FROM dbo.Class WHERE ClassName = @ClassName);
    BEGIN TRANSACTION;
    WITH Counts AS (
        SELECT StudentID, COUNT(*) AS Classes, MAX(CASE WHEN ClassID = @ClassID THEN 1 ELSE 0 END) AS InTarget
        FROM dbo.StudentClass
        GROUP BY StudentID
    )
    DELETE s
    FROM dbo.Student AS s
    JOIN Counts AS c ON c.StudentID = s.ID
    WHERE c.Classes = 1 AND c.InTarget = 1;
    DELETE FROM dbo.StudentClass WHERE ClassID = @ClassID;
    COMMIT TRANSACTION;
END;
GO
EXEC dbo.ResetPuzzle;

INSERT INTO dbo.Student (ID, StudentName) VALUES (4, 'Priya');

SET STATISTICS IO ON;

EXEC dbo.RemoveClass @ClassName = 'English';

SET STATISTICS IO OFF;

SELECT ID, StudentName FROM dbo.Student ORDER BY ID;

The fix costs 5 reads, one more than the cheap answer, and it keeps Priya. The procedure runs both deletes in one transaction, so they succeed or fail together. Priya stays. A student can only be deleted by the procedure when the student has an enrollment in the cancelled class. A student with no enrollment never enters the group, so nothing touches that row.

You could argue that a student with no classes is useless data, so deleting it does no harm. Maybe it is. In a school, a student exists before the first enrollment. A delete that removes that record loses real information. The rules say what to delete, and the script must obey them.

What to Remember

Test a delete with the rows it should not touch. For every rule, add a row that the rule must leave alone. Then run the delete. A cheap answer that passes a small test has proven nothing yet. A delete that can delete the wrong row needs more than a read count.

Three checks cover most deletes. Include a row the rule must remove. Include a row the rule must keep. Include the empty case, where nothing matches. Run all three before the delete goes near real data.

When you finish with the puzzle, run the cleanup script.

USE master;
GO
IF DB_ID(N'StudentClassFollowUpDemo') IS NOT NULL
BEGIN
    ALTER DATABASE StudentClassFollowUpDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE StudentClassFollowUpDemo;
END;

A passing test is not proof, it is only the data you thought of.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.