>I have a SQL command like this:
>
>

>SELECT Menu.Numero,COUNT(*) AS RecordCount
> FROM Menu
> INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu
> INNER JOIN MenuPrivilege ON Menu.Numero=MenuPrivilege.NoMenu
> INNER JOIN MenuRole ON Menu.Numero=MenuRole.NoMenu
> INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity AND
>  MenuPrivilege.NoType=Member.NoType AND MenuRole.NoRole=Member.NoRole
> WHERE Menu.Numero NOT IN (SELECT Menu.Numero FROM MenuPrivilegeExceptionCase
> INNER JOIN Menu ON MenuPrivilegeExceptionCase.NoMenu=Menu.Numero
> WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero)
> GROUP BY Menu.Numero
>UNION
> SELECT Menu.Numero,COUNT(*) AS RecordCount
> FROM Menu
> INNER JOIN MenuPrivilegeExceptionCase ON Menu.Numero=MenuPrivilegeExceptionCase.NoMenu AND
>  MenuPrivilegeExceptionCase.Enabled=1
> INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu
> INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity
> WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero
> GROUP BY Menu.Numero
>

>
>This gets me two records for each group by. Each two-record set contains a count for the first part of the SQL command and another count for the second part of the SQL command. So, I can obtain something like this:
>
>1 25
>1 108
>2 499
>2 105
>
>The goal is to update the Menu.Count field with the sum of those two counts.
>
>So, I tried something like this:
>
>

>UPDATE Menu SET Count=Temp.RecordCount FROM Menu JOIN
>(SELECT Menu.Numero,COUNT(*) AS RecordCount
> FROM Menu
> INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu
> INNER JOIN MenuPrivilege ON Menu.Numero=MenuPrivilege.NoMenu
> INNER JOIN MenuRole ON Menu.Numero=MenuRole.NoMenu
> INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity AND
>  MenuPrivilege.NoType=Member.NoType AND MenuRole.NoRole=Member.NoRole
> WHERE Menu.Numero NOT IN (SELECT Menu.Numero FROM MenuPrivilegeExceptionCase
> INNER JOIN Menu ON MenuPrivilegeExceptionCase.NoMenu=Menu.Numero
> WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero)
> GROUP BY Menu.Numero
>UNION
> SELECT Menu.Numero,COUNT(*) AS RecordCount
> FROM Menu
> INNER JOIN MenuPrivilegeExceptionCase ON Menu.Numero=MenuPrivilegeExceptionCase.NoMenu AND
>  MenuPrivilegeExceptionCase.Enabled=1
> INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu
> INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity
> WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero
> GROUP BY Menu.Numero) AS Temp ON Temp.Numero=Menu.Numero
>

>
>But, that did not give me the expected result as it updates the Menu.Count field but with only the first count. There has to be something simple to adjust here to obtain the desired result.

UPDATE Menu
  SET Count=ISNULL(A.RecordCount,0)+ISNULL(B.RecordCount,0)
FROM
(SELECT Menu.Numero,COUNT(*) AS RecordCount
	FROM Menu
	INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu
 INNER JOIN MenuPrivilege ON Menu.Numero=MenuPrivilege.NoMenu
 INNER JOIN MenuRole ON Menu.Numero=MenuRole.NoMenu
 INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity AND
  MenuPrivilege.NoType=Member.NoType AND MenuRole.NoRole=Member.NoRole
 WHERE Menu.Numero NOT IN (SELECT Menu.Numero FROM MenuPrivilegeExceptionCase
 INNER JOIN Menu ON MenuPrivilegeExceptionCase.NoMenu=Menu.Numero
 WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero)
 GROUP BY Menu.Numero) A
FULL JOIN
 (SELECT Menu.Numero,COUNT(*) AS RecordCount
 FROM Menu
 INNER JOIN MenuPrivilegeExceptionCase ON Menu.Numero=MenuPrivilegeExceptionCase.NoMenu AND
  MenuPrivilegeExceptionCase.Enabled=1
 INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu
 INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity
 WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero
 GROUP BY Menu.Numero) B ON B.Numero = A.Numero
WHERE Menu.Numero = ISNULL(A.Numero,B.Numero)