I would greatly appreciate any help that you can provide.  I have the
following dml/query (part of a stored proc) that runs perfectly in SQL
2000.  However, when I run it in SQL 2005 I get the following error:

"An aggregate may not appear in the set list of an UPDATE statement"

Here is the dml/query:

   UPDATE #MyTable
   SET    EndDate = (SELECT DATEADD (yy, 2, TranDate)
   FROM   #MyTable AS a
                   WHERE  a.BaseCode = #MyTable.BaseCode
                   AND    a.FromCode = #MyTable.FromCode
                   AND    a.ExchangeCode = #MyTable.ExchangeCode
                   AND    a.TranDate = max(#MyTable.TranDate))
   WHERE EndDate IS NULL

Any and all help would be greatly appreciated.

TFD

LineVoltageHalogen wrote:
Show quoteHide quoteThe MAX() expression references the table being updated so it will
always refer to a single row. For that reason I think it can be safely
ignored but it's probably correct that the aggregate shouldn't be
permitted at all. 2005 tightens up syntax checking in a number of
places so there are one or two catches like this one.

As far as I can see, the following should be equivalent and should work
equally well in both versions although it doesn't look very useful to
me. I'm not certain what you intended by your UPDATE statement. If I'm
wrong here, please provide us with the usual DDL, sample data and
explain what you want.

UPDATE #MyTable
   SET    EndDate = (SELECT DATEADD (yy, 2, TranDate)
   FROM   #MyTable AS a
                   WHERE  a.BaseCode = #MyTable.BaseCode
                   AND    a.FromCode = #MyTable.FromCode
                   AND    a.ExchangeCode = #MyTable.ExchangeCode
                   AND    a.TranDate = #MyTable.TranDate)
   WHERE EndDate IS NULL ;

Thanks Mate, I will give it a spin and post the results here.

TFD

(Non) Updatable view problem
Help with this trigger
Sum Total Hours and Minutes in a report
Selecting rows with highest count value
how to represent very long primary key