Update using select and join..

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)

Update using select and join..

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-05-26 : 08:51:05

Hello, Need some help again...If figured this proc out. the first part is working,
the second part..how can I get the update function correct, for every record in the Temp_dock_view tabel....

here's my code so far...

-- Clean up table, only 13 rows...
DELETE FROM Temp_DockView

-- get info on join from tables, This works already
INSERT Temp_DockView (NR, STATUS, SHIPMENT_CODE, BESTEMMING, AANTALPALLETS, MAX_PALLETS, GEWICHTPALLETS, MAX_LOAD, LAND, TOTALPALLETSONFLOOR)
SELECT dbo.INFO_DOCK.NR, dbo.INFO_SHIPMENT.STATUS, dbo.INFO_SHIPMENT.CODE AS SHIPMENT_CODE, dbo.INFO_SHIPMENT.BESTEMMING,
dbo.INFO_SHIPMENT.AANTALPALLETS, dbo.INFO_DEPOT.MAX_PALLETS, dbo.INFO_SHIPMENT.GEWICHTPALLETS, dbo.INFO_DEPOT.MAX_LOAD,
dbo.INFO_SHIPMENT.LAND, 0
FROM dbo.INFO_DOCK INNER JOIN
dbo.INFO_SHIPMENT ON dbo.INFO_DOCK.SHIPMENT = dbo.INFO_SHIPMENT.CODE INNER JOIN
dbo.INFO_DEPOT ON dbo.INFO_DOCK.BESTEMMING = dbo.INFO_DEPOT.BESTEMMINGS_CODE
WHERE dbo.INFO_DOCK.NR > 0
ORDER BY dbo.INFO_DOCK.NR

--Get total pallets and update Temp_DockView, select is OK, but update ?????
UPDATE Temp_DockView SET TOTALPALLETSONFLOOR =
(SELECT COUNT(*) AS TotalPalletsOnFloor
FROM dbo.Temp_DockView INNER JOIN
dbo.INFO_SSCC ON dbo.Temp_DockView.BESTEMMING = dbo.INFO_SSCC.BESTEMMINGSCODE
WHERE dbo.INFO_SSCC.SHIPMENTNR <= 20) AND (dbo.INFO_SSCC.SSCC_STATUS = 1) )

-- Return recordset
SELECT * FROM dbo.Temp_DockView

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-26 : 09:43:34

Hi:

You will need to wrap your aggregaring query into a sub-query and join the table that needs updating with it, something like this:

UPDATE Temp_DockView SET TOTALPALLETSONFLOOR = TotalPalletsOnFloorA
FROM Temp_DockView
INNER JOIN
(SELECT BESTEMMINGSCODE, COUNT(*) AS TotalPalletsOnFloorA
FROM dbo.INFO_SSCC WHERE SHIPMENTNR <= 20 AND SSCC_STATUS = 1) A
ON A.BESTEMMINGSCODE = TempDockView.BESTEMMING

OS

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-05-27 : 15:32:51

thanks for your help. Found a solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources