01 October 2010
Warning from player regarding 30 September answer (1444)
In the 30 September quiz on RETURNING, I scored as correct the choice that performed the update and then, separately, issued a query to get information back regarding the impact of the update. I wrote in the explanation: This solution issues the update and then in a separate SQL statement retrieves the part number of the row that was just modified - but only by reproducing the logic ("partname = UPPER (partname)") in the WHERE clause. This means that I have introduced repetition in my code, and also inefficiency (an extra context switch). This is logically equivalent to using the RETURNING clause, but definitely inferior to RETURNING. A player wrote to say: In your explanation of answer 3 for the September 30 quiz, you note that this solution is repetitious and less efficient than using the RETURNING clause. I suggest that it is even more important to note that this is only 'accidentally correct' - if the starting data had included other rows where partname = upper(partname) such as '2 GB RAM' then this code would not have worked. This is a good point, both specifically for this answer and more generally for the quizzes: in order to make the quiz questions and choices unambiguous, I construct very "small worlds" of data and code. I seek to demonstrate and exercise your knowledge on fairly specific features. This means that you should be able to trust the lesson of the quiz, but you should not adopt/follow the code samples as any sort of general pattern for a solution to your real-world problems. In the case of this choice in the quiz, I should be (and will update the answer so that I am) more explicit in drawing out the lesson here: If you use a SELECT after your DML statement to determine if the correct changes were made, you need to be very careful about how you specify the WHERE clause of your query to be sure that you identify the same rows that were (possibly) changed.