aggregate functions in UPDATE statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

aggregate functions in UPDATE statement

Author

Topic

vgurgov
Starting Member

12 Posts

Posted - 2007-02-20 : 17:52:07

Hi,
I am trying to execute this query, but SQL Server tells me that 'An aggregate may not appear in the set list of an UPDATE statement.'

This is the query:

UPDATE categories
SET 
  categ_name='Books',
  priority=MAX(priority)+1
WHERE categ_id=12

Can you recommend a way around this?

Thanks,
VG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-20 : 18:10:29

What do you want the query to do? The way you have it, you want to find the maximum value of the priority column in the categories table and then set the priority of every category to that value plus one. So after your update every category will have the same priority. Is that what you want?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 18:23:33

use sub query ?


UPDATE categories
SET 
  categ_name   = 'Books',
  priority     = (select MAX(priority) from categories) + 1
WHERE categ_id = 12

vgurgov
Starting Member

12 Posts

Posted - 2007-02-20 : 20:05:31

khtan, the subquery worked. Thanks a lot!

snSQL, no, i wanted the query to updated only 1 record. The query set the lowest priority (highest number) to the category upon update.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources