tsql help!

simple update, I want to update max_seq with max(0rdr_seq) from another table.
how do you?

update h
set max_seq = d.max(ordr_seq)
from h_drug_stage_dup h
join drug_ordr_stage d
on h.patkey = d.patkey and
h.ordr_dtm= d.ordr_dtm and
h.h_drug = d.h_Drug

Hi

Corelated sub-query:

UPDATE SalesSum SS
SET YTDsales=
(
SELECT sum(qty) As YTDsales
FROM ProductSales PS
WHERE OrderDate BETWEEN '1-jan-01' AND getdate()
AND PS.ProductID=SS.ProductID
)

The last line forces the inner query to recalculate for EACH product in the outer query.


Dale