Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

March 10, 2003

Oracle v SQL Server, Part 8

The subject of todays post is updating several columns at once. Whilst this is possible in T-SQL I can't find a way to do this with a sub-query. Its quite simple in Oracle;

UPDATE tableA
SET (columna, columnb) =
    ( SELECT sum(valueA), count(valueB)
      FROM tableB
      WHERE tableB.foreignKey = tableA.key )

But when I try it in SQL Server all I get is an error message stating that An aggregate may not appear in the set list of an UPDATE statement.

Any suggestions?

Posted by Andy Todd at March 10, 2003 03:24 PM

Comments