Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Fix in a Sql Statement for Sql Server 2008

Posted on 2014-08-04
5
Medium Priority
?
218 Views
Last Modified: 2014-08-04
I have some code. What I am trying to do is update a certain field to the max + 1

this is the code  that I have
 
update tblBNKrecon
set groupedpmtid = (MAX(GroupedPmtID)+1), newdesc = 'FEHBP' 
where groupedpmtid = 811209465 and [desc] = 'FEHBP HCCLAIMPMT'

Open in new window


the error I get is An aggregate may not appear in the set list of an UPDATE statement.
Thanks in advance
0
Comment
Question by:damixa
  • 2
  • 2
5 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 40239406
Please try this:

update tblBNKrecon
set groupedpmtid = (select MAX(GroupedPmtID) from tblBNKrecon) +1, newdesc = 'FEHBP'
where groupedpmtid = 811209465 and [desc] = 'FEHBP HCCLAIMPMT'
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40239409
update tblBNKrecon
set groupedpmtid = (select MAX(GroupedPmtID) + 1 from tblBNKrecon), newdesc = 'FEHBP'
where groupedpmtid = 811209465 and [desc] = 'FEHBP HCCLAIMPMT'
0
 

Author Comment

by:damixa
ID: 40239443
Thanks guys,
Im also getting a warning
Warning: Null value is eliminated by an aggregate or other SET operation.

everything seems to be working though. Can you tell me what this means?
0
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 1000 total points
ID: 40239447
Essentially there is a GroupedPmtID that is null.  It doesn't know what to do with it, so it ignores it.  You can fix that by using the following:
update tblBNKrecon
set groupedpmtid = (select MAX(isnull(GroupedPmtID,0)) + 1 from tblBNKrecon), newdesc = 'FEHBP' 
where groupedpmtid = 811209465 and [desc] = 'FEHBP HCCLAIMPMT'

Open in new window

0
 

Author Closing Comment

by:damixa
ID: 40239451
Thanks a ton
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question