SQL Server Performance

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

Discussion in 'General Developer Questions' started by dyckwal, May 26, 2003.

  1. dyckwal New Member

    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
  2. joechang New Member

    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


  3. vbkenya New Member

    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
  4. dyckwal New Member

    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=':D' />]

Share This Page