Update - help

  • Hi,

    I need some help with an update query .

    Create table #projects (

    ID INT IDENTITY(1,1) ,

    OrderID INT,

    ProjectID INT,

    Project VARCHAR(100),

    ProductID INT,

    Product Varchar(100),

    Amount Decimal (18,2),

    Unit Decimal (18,2)

    )

    INSERT INTO #projects (OrderID,ProjectID,Project,ProductID,Product,Amount,Unit)

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',7875.00,189.109

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',7875.00,189.109

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',7075.00,159.109

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',7075.00,159.109

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00

    UNION ALL

    SELECT 3999,4399,'Lab Sea',247,' PROC MIG',8418.35,11224.47

    UNION ALL

    SELECT 3999,4399,'Lab Sea',247,' PROC MIG',8418.35,11224.47

    UNION ALL

    SELECT 3999,3400,'Lab Sea 22',192,' PROC MIGttt ',418.35,1224.47

    SELECT * FROM #projects

    DROP TABLE #projects

    WHEN OrderID,ProjectID AND ProductID ARE the same , I want update amount and unit fields OF the same combination TO 0 IN ALL rows EXCEPT 1 ,

    example FOR OrderID 5392,ProjectID 2989 AND productID 357 , this should be displayed AS

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',7875.00,189.109

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00

    example FOR OrderID 5392,ProjectID 2989 AND productID 367 , this should be displayed AS

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',7075.00,159.109

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00

    example FOR OrderID 3999,ProjectID 4399 AND productID 247 , this should be displayed AS

    SELECT 3999,4399,'Lab Sea',247,' PROC MIG',8418.35,11224.47

    UNION ALL

    SELECT 3999,4399,'Lab Sea',247,' PROC MIG',0.00,0.00

    so the final resultset should be LIKE the following

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',7875.00,189.109

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',7075.00,159.109

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00

    UNION ALL

    SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00

    UNION ALL

    SELECT 3999,4399,'Lab Sea',247,' PROC MIG',8418.35,11224.47

    UNION ALL

    SELECT 3999,4399,'Lab Sea',247,' PROC MIG',0.00,0.00

    UNION ALL

    SELECT 3999,3400,'Lab Sea 22',192,' PROC MIGttt ',418.35,1224.47

    Thanks,

    PSB

  • How about this:

    ;WITH CTE AS

    (

    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY OrderID,ProjectID,ProductID ORDER BY OrderID,ProjectID,ProductID, Amount DESC)

    FROM #projects

    )

    SELECT OrderID

    ,ProjectID

    ,Project

    ,ProductID

    ,Product

    ,Amount = CASE WHEN RN = 1 THEN Amount ELSE 0 END

    ,Unit = CASE WHEN RN = 1 THEN Unit ELSE 0 END

    FROM CTE

    ORDER BY OrderID,ProjectID,ProductID, RN;

  • If you don't mind can you send an update query instead of the select ?

    Thanks,

    PSB

  • -- Updating a single-table CTE updates the underlying table, so the change is straightforward:

    -- (Note that you need to order by Amount DESC to ensure the values are in row 1 for each partition.)

    ;WITH CTE AS

    (

    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY OrderID,ProjectID,ProductID ORDER BY OrderID,ProjectID,ProductID, Amount DESC)

    FROM #projects

    )

    UPDATE CTE

    SET Amount = CASE WHEN RN = 1 THEN Amount ELSE 0 END

    ,Unit = CASE WHEN RN = 1 THEN Unit ELSE 0 END;

    -- View results:

    SELECT * FROM #projects ORDER BY OrderID,ProjectID,ProductID;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply