can this query be rewritten?

update CMS_RISK_SCORES
set MAX_MCARA_RISK_RTE = (select max(MCARA_RISK_RTE) from XTAW0200_MEM_DTL A
where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM),
MAX_MCARD_RISK_ADJ_RTE = (select max(MCARD_RISK_ADJ_RTE) from XTAW0200_MEM_DTL A
where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM)

Can I get the same results with one join instead of two without creating a temporary table?

Thanks much.

:confused:update CMS_RISK_SCORES
set MAX_MCARA_RISK_RTE = MaxValues.MCARA_RISK_RTE,
MAX_MCARD_RISK_ADJ_RTE = MaxValues.MCARD_RISK_ADJ_RTE
from CMS_RISK_SCORES
inner join --MaxValues
(select HIC_NUM,
max(MCARA_RISK_RTE) as MCARA_RISK_RTE,
max(MCARD_RISK_ADJ_RTE) as MCARD_RISK_ADJ_RTE
from XTAW0200_MEM_DTL
group by HIC_NUM) MaxValues
on CMS_RISK_SCORES.HIC_NUM = MaxValues.HIC_NUM|||Thank You.|||While there is a difference in syntax, I don't think there will be any significant difference in execution plan between the two statments. SQL Server is very good at combining redundant queries like this.

-PatPsql