update statement with aggregate value

[timscronin]

I have table A which has and accountid,df_date1,df_date2. The table is a demographic one which has 1 record for each account
I have a table B which I need to populate from the first df_date1 fields in table A. Table B which is normalized and has an accountid and a df_date1 field but may have several records per accountid. I need the max(date) from this table. I wanted to do an update statement like below

update A
set df_date1
= max(df_date1) from b
where a.account_id = b.account_id

I get the error message
Server: Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement.

Is there another way to do this with a subselect and update?

 

[Golom]

You're almost there
[tt]
update A
set A.df_date1
= (Select max(df_date1) from b
where A.account_id = b.account_id)
[/tt]