How to do the insert using joins.....

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

you should provide the table script including primary keys and indexes

first, the placement of the parenthesis is confusing

if your update did not involve aggregates, just write
UPDATE x SET col1 = ...
FROM ...

the ... after the FROM is just the same as you where doing a SELECT

with the aggregate, i think you need something like the following:

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

With your UPDATE query:

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) )


There is a loose bracket somewhere in your code (Check where my crude arrow is pointing)

In any case.....

All rows would be updated to the same TOTALPALLETSONFLOOR value if the SELECT value returned 1 value. Otherwise returning more than 1 value should throw an error.


Try this instead....

UPDATE Temp_DockView SET TOTALPALLETSONFLOOR =
(SELECT COUNT(*) AS TotalPalletsOnFloor
FROM dbo.Temp_DockView td INNER JOIN dbo.INFO_SSCC iss
ON td.BESTEMMING = iss.BESTEMMINGSCODE
WHERE iss.SHIPMENTNR <= 20 AND iss.SSCC_STATUS = 1
AND td.BESTEMMING=Temp_Dockview.BESTEMMING)


No guarantees for you, but would work fine for me. I tested this with the northwind database since I did not have your tables.


NHO

Thanks for your tips. I have the solution for my problem.<br />Hope I can help you sometime....[<img src='/community/emoticons/emotion-2.gif' alt='' />]