Transact-SQL Cookbook
Corrections
What follows is a compendium of all errors that I'm aware of in the Transact-SQL Cookbook. All these errors may not be present in your copy. O'Reilly corrects known errors with each printing, so the more recently your book was printed, the fewer of these errors will apply.
Chapter 2: Sets
The Students Example, Page 12
The last paragraph on the page refers to a file named ch01.ImplementingSetDifference.objects.sql.
Replace ch01 with ch02. The correct filename is: ch02.ImplementingSetDifference.objects.sql.
Recipe 2.4, Pages 16-18
I neglected to include the term paper score in the HAVING clauses of
my solution query shown on page 16. Following is the first half of that
UNION query as it really should be. Note the addition of sn.Score=so.Score
to the HAVING clause of the subquery.
SELECT so.*, COUNT(*) DupeCount, 'StudentsOct' TableName FROM StudentsOct so GROUP BY so.CourseId, so.StudentName, so.Score, so.TermPaper HAVING NOT EXISTS ( SELECT sn.*, COUNT(*) FROM StudentsNov sn GROUP BY sn.CourseId, sn.StudentName, sn.Score, sn.TermPaper HAVING sn.CourseId=so.CourseId AND sn.TermPaper=so.TermPaper AND sn.StudentName=so.StudentName AND sn.Score=so.Score COUNT(*) = COUNT(ALL so.CourseId))
Likewise, the second query in the UNION should use so.Score=sn.Score
in the HAVING clause.
Chapter 3: Data Structures
SELECT FROM Matrices, Page 52
On page 52 we show the output of a SELECT against the Matrices
table. The first column of the output is entitled Name. That's
incorrect. The correct column name is Matrix. Refer back
to the CREATE TABLE statement at the top of the page, and
also refer forward to the matrix examples later in the chapter. In all
those cases, the name of the column containing the matrix name is Matrix.
Chapter 6: Audit Logging
Page 162, last line
The text contains the word "vales". That word should be "values".
Chapter 7: Importing and Transforming Data
Page 233, under "Deleting duplicate rows"
The following text and example is incorrect:
The statement in the following example deletes duplicate rows, arbitrarily retaining one record for each fruit and color combination:
DELETE FROM Warehouse WHERE BookId NOT IN ( SELECT MAX(BookId) FROM Bookstore GROUP BY Name, Type HAVING COUNT(*) > 1)The subquery in this example identifies the highest id number value for each name and color combination. This identifies the row that we are arbitrarily going to keep. All other rows with that name and color combination are deleted. The key here is that id is unique for all rows in a name and type combination.
The following text and example is correct:
The statement in the following example deletes duplicate rows, arbitrarily retaining one record for each name and type combination:
DELETE BookStore WHERE EXISTS( SELECT Name FROM BookStore AS b WHERE BookStore.Name = b.Name And BookStore.Type = b.Type GROUP BY b.Name, b.Type HAVING COUNT( * ) > 1 And MAX( B.BookId ) > BookStore.BookId )The subquery in this example identifies the highest id number value for each name and type combination. This identifies the row that we are arbitrarily going to keep. All other rows with that name and type combination are deleted. The key here is that id must be unique for each row in the group for a name and type combination.