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

 

[john76]

Try this:

UPDATE dbo.TmpMaster30_1
    SET    FirstProm = min(h.Prom) [COLOR=green]-- Note that I removed the "t." from the FirstProm field[/color]
    FROM   dbo.TmpMaster30_1 t,
           dbo.FACT_OF_PROM_ARCHIVE h  [COLOR=green]-- you also had an extra "," on this line, I'm sure that would have fouled things up as well.[/color]
    WHERE  t.PersonNo = h.PersonNo

I don't believe that SQL Server allows alias names on the variable being set.

Hope this helps,
John

 

[amorous]

Try Something like this:

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

-VJ