Updating /Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating /Grouping

Author

Topic

Saeed
Starting Member

39 Posts

Posted - 2003-05-29 : 22:43:46

Just trying to group by mccn & WarehouseN from #ValidRoll and find the max(dateReceved) and update it in #Report...

update #Report set #Report.LastReceipt = ( select max(a.DateReceived) from #ValidRoll a,#Report b
where a.mccn=b.mccn and a.WarehouseN=b.WarehouseN)
where #Report.mccn =#ValidRoll.mccn and
#Report.WarehouseN=#ValidRoll.WarehouseN

But SQL does not like this

Saeed
Starting Member

39 Posts

Posted - 2003-05-29 : 23:12:06

This seems to be a clearer solution however without the fun() max , it works but with it , it does not

update #Report set #Report.LastReceipt = max(#ValidRoll.DateReceived)
where a.mccn =b.mccn and
a.WarehouseN =b.WarehouseN

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-30 : 07:55:14

Hi Saeed:

Try this -

UPDATE #Report SET #Report.LastReceipt = X.DateRec
FROM #Report INNER JOIN
(
SELECT a.mccn, a.WarehouseN, MAX(a.DateReceived) AS DateRec from #ValidRoll a INNER JOIN #Report b
ON a.mccn=b.mccn and a.WarehouseN=b.WarehouseN
GROUP BY a.mccn, a.WarehouseN) X
ON #Report.mccn = X.mccn and
#Report.WarehouseN=X.WarehouseN

HTH
Owais

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-30 : 08:09:32

-- This select gives the Max Date Received for all mccn

SELECT A.mccn, MAX(A.DateReceived) as MaxDateReceived

FROM #ValidRoll A

JOIN #Report B ON A.mccn = B.mccn AND A.WarehouseN = B.WarehouseN

GROUP BY A.mccn

-- Now for the update

UPDATE #Report

SET LastReceipt = MaxDateReceived

FROM #Report

INNER JOIN (

-- Insert the first query above here

) B ON B.mccn = #Report.mccn

HTH,

Sam

* Owais' query is correct.. Sam

Edited by - SamC on 05/30/2003 08:11:07

joseph
Starting Member

10 Posts

Posted - 2003-06-22 : 14:57:55

try:
update #Report
set LastReceipt =(select max(DateReceived)
from #ValidRoll
where #Report.mccn =mccn and
#Report.WarehouseN=WarehouseN )
It must work

Saeed
Starting Member

39 Posts

Posted - 2003-06-22 : 19:24:00

thanks to all that responded

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources