ASE Quiz Question Randomizer
 
Randomly selected quiz question (want a different question? Click here):


January 2004
This question is about a rather simple update. We have the following tables t1 and t2; both have two int columns and contain two rows:
1> select * from t1
2> go
 a           b1
 ----------- -----------
           0         100
           0         200

(2 rows affected)


1> select * from t2
2> go

 b2          c
 ----------- -----------
         100           3
         100           5

(2 rows affected)
What we want to do is assign the sum of column t2.c to t1.a for matching values in columns t1.b1 and t2.b2. In other words, we want the value 8 (=3+5) assigned to column t1.a for the row where t1.b1 = 100.
The following query is written to accomplish this:
update t1
set a = sum(c)
from t1, t2 
where b1 = b2 
  and b1 = 100
Couldn't be simpler, right? But does this query work correctly?

Answer:
Perhaps surprisingly, this update query updates both rows in t1:
1> update t1
2> set a = sum(c)
3> from t1, t2 
4> where b1 = b2 
5>   and b1 = 100
6> go
(2 rows affected)


1> select * from t1
2> go
 a           b1
 ----------- -----------
           8         100
           8         200

(2 rows affected)
In fact, if t1 had contained more rows, this query would have updated all those rows in the same way, irrespective of the value in their b1 column: the predicates where b1 = b2 and b1 = 100 are completely ignored by this update!

The problem lies in the sum() aggregate in the set clause. When replacing this aggregate by a constant or a variable, the update works as expected: it performs the specified join and update only 1 row (try this yourself!).
However, with an aggregate in the set clause, the update is effectively processed as follows (you can also see this with showplan on):
update t1
set a = (select sum(c)
         from t1, t2 
         where b1 = b2 
           and b1 = 100)
This is an interesting case because the ASE documentation does not seem to describe this particular situation at all (well, at least I couldn't find it...). However, the docs do say that aggregates are allowed only in the select list or in a having clause, so it feels a bit funny that an update-with-aggregate is accepted by ASE after all (if anyone knows more about the background of this issue, I'll be glad to post it here).

The correct way to write this update is to use a correlated subquery (this is also ANSI SQL-compliant):
update t1
set a = (select sum(c) 
         from t2 
         where t1.b1 = b2)
where b1 = 100
Alternatively (and more clumsy), you can split up the query and replace the aggregate by a variable:
declare @s int

select @s = sum(c)
from t1, t2 
where b1 = b2 
  and b1 = 100

update t1
set a = @s
from t1, t2 
where b1 = b2 
  and b1 = 100
Instead of a variable, a noncorrelated expression subquery can also be used (note how the line in bold is essential to avoid updating all rows again):
update t1
set a = (select sum(c)
         from t1, t2 
         where b1 = b2 
           and b1 = 100)
where b1 = 100  -- essential!
Note that the fipsflagger (i.e. set fipsflagger on) doesn't give any clues to this particular problem. It will generate a warning, indicating that a from clause in an update is non-standard SQL. However, the same warning is generated when replacing the aggregate by a variable, so the fipsflagger isn't particularly useful here.

Lesson learned: Don't be satisfied just because your update statement seems to run fine. Always check the exact rows and columns being updated!
How would you rate this ASE quiz question?
Great    OK    Average    Boring     


More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.


 
 This document is located at www.sypron.nl/quizrand.html