包含聚合的更新语句在SQL Server中不起作用 [英] Update statement containing aggregate not working in SQL server

问题描述

我希望有人可以在这里帮助我的语法. 我有两个表ansicache..encounters和ansicache..x_refclaim_Table

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

encounters表的encounter列与x_refclaim_table中的patacctnumber列匹配.

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

但是，有时patacctnumber可以在x_refclaim_table中以不同的服务日期出现两次(列iar_servicedate).

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

我正在尝试将encounters表的admitted列更新为iar_servicedate的最大值，其中encounters表中的encounter = x_refclaim表中的patacctnumber

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
      )

尽管它仍然失败:

第157条消息，第15级，状态1，第1行 聚合可能不会出现在UPDATE语句的设置列表中.

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

我尝试做其他一些事情，例如声明ID，但无法正常工作.

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

推荐答案

使用相关子查询

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

这篇关于包含聚合的更新语句在SQL Server中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！