ID Number: Q75292
1.10
OS/2
buglist1.10
SYMPTOMS
When using the SET clause in an UPDATE statement in the following
manner, unpredictable results are returned:
update test
set col2=max(col2) + 1
where col1=6
CAUSE
Aggregate functions cannot be used in the SET clause in an UPDATE
statement (unless included as part of a subquery). However,
improper use of the max() function in the SET clause does not
produce a syntax error; the query executes and gives unpredictable
results.
WORKAROUND
The following query will give the expected results
update test
set col2 = ((select max(col2)) + 1)
where col1 = 6
STATUS
Microsoft has confirmed this to be a problem in SQL Server version
1.1. We are researching this problem and will post new information
here as it becomes available.