update statement with aggregates

[andycape]

Is it possible to include aggregates in an update statement?

I want to do the following that is not being allowed :


Update dbo.TmpMaster30_1
set t.FirstProm = min(h.Prom)
from dbo.TmpMaster30_1 t,
dbo.FACT_OF_PROM_ARCHIVE h,
where t.PersonNo = h.PersonNo

 

[nicatt]

I am not an expert but did you try....

Update dbo.TmpMaster30_1
set FirstProm = select (min(h.Prom)
from dbo.TmpMaster30_1 t,
dbo.FACT_OF_PROM_ARCHIVE h,
where t.PersonNo = h.PersonNo)

 

[nikhilparchure]

nicatt the way suggested is correct but will update all the rows of the table

Update dbo.TmpMaster30_1
set FirstProm = ( select min(h.Prom)
from dbo.TmpMaster30_1 t,
dbo.FACT_OF_PROM_ARCHIVE h,
where t.PersonNo = h.PersonNo
)
From dbo.FACT_OF_PROM_ARCHIVE
Where TmpMaster30_1.PersonNo =
FACT_OF_PROM_ARCHIVE.PersonNo

Hope the above is the correct solution