Fix in a Sql Statement for Sql Server 2008

Posted on 2014-08-04
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
Question by:damixa
    LVL 68

    Assisted Solution

    Please try this:

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

    Expert Comment

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

    Author Comment

    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?
    LVL 39

    Accepted Solution

    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


    Author Closing Comment

    Thanks a ton

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now