CODEWITHSUNDEEP
sqlsql-servermaxaggregate
Robby Johnston
Robby Johnston

Reputation: 191

Update statement containing aggregate not working in SQL server

I am hoping someone can help my syntax here. I have two tables ansicache..encounters and ansicache..x_refclaim_Table

The encounters table has an encounter column that matches the patacctnumber column in the x_refclaim_table.

However, sometimes the patacctnumber can show up twice in the x_refclaim_table with different service dates (column iar_servicedate).

I am trying to update the encounters table, admitted column to the maximum value of the iar_servicedate where the encounter in encounters table = patacctnumber in x_refclaim table

 update ansicache..ENCOUNTERS 
       set ADMITTED=max(IAR_ServiceDate) 
 from
     (
       ansicache..ENCOUNTERS e (nolock) 
          join 
       ansicache..x_refClaim_table x (nolock)
          on e.ENCOUNTER=x.PatAcctNumber
      )

It keeps failing though:

Msg 157, Level 15, State 1, Line 1 An aggregate may not appear in the set list of an UPDATE statement.

I tried doing some other stuff like declaring an ID but couldn't get it working.

Upvotes: 3

Views: 11795

Answers (2)

roman
roman

Reputation: 117510

you can preaggreagate your data before update.

update ansicache..ENCOUNTERS set
    ADMITTED = x.IAR_ServiceDate
from ansicache..ENCOUNTERS as e
    inner join (
        select
            x.PatAcctNumber, max(x.IAR_ServiceDate) as IAR_ServiceDate
        from ansicache..x_refClaim_table as x
        group by x.PatAcctNumber
    ) as x on x.PatAcctNumber = e.ENCOUNTER

It's usually more preferred method for me than subquery, because you can use max(...) several times if you need it, or you can use other aggregates additionally, so it's easier to maintain this query in the future:

update ansicache..ENCOUNTERS set
    ADMITTED = x.IAR_ServiceDate,
    ADMITTED2 = dateadd(dd, 5, x.IAR_ServiceDate2)
from ansicache..ENCOUNTERS as e
    inner join (
        select
            x.PatAcctNumber,
            max(x.IAR_ServiceDate) as IAR_ServiceDate,
            min(x.IAR_ServiceDate) as IAR_ServiceDate2
        from ansicache..x_refClaim_table as x
        group by x.PatAcctNumber
    ) as x on x.PatAcctNumber = e.ENCOUNTER

Another way to do this is to put max into apply:

update ansicache..ENCOUNTERS set
    ADMITTED = x.IAR_ServiceDate,
    ADMITTED2 = dateadd(dd, 5, x.IAR_ServiceDate2)
from ansicache..ENCOUNTERS as e
    cross apply (
        select
            max(x.IAR_ServiceDate) as IAR_ServiceDate,
            min(x.IAR_ServiceDate) as IAR_ServiceDate2
        from ansicache..x_refClaim_table as x
        where x.PatAcctNumber = e.ENCOUNTER
    ) as x

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453648

Use a correlated sub query

UPDATE e
SET    ADMITTED = (SELECT max(IAR_ServiceDate)
                   FROM   ansicache..x_refClaim_table x
                   WHERE  e.ENCOUNTER = x.PatAcctNumber)
FROM   ansicache..ENCOUNTERS e

Upvotes: 9

Related Questions