Update Problem

  • I have 2 tables #Products and #PrICE:

    CREATE TABLE #Products

    (

    Product_Name VARCHAR(20),

    Qty INT,

    Price DECIMAL(10,2)

    )

    CREATE TABLE #PrICE

    (

    Product_Name VARCHAR(20),

    Pr DECIMAL(10,2)

    )

    INSERT INTO #PrICE

    SELECT 'Prod1',2

    INSERT INTO #PrICE

    SELECT 'Prod2',4

    INSERT INTO #Products

    SELECT 'Prod1',2,2.5

    UNION

    SELECT 'Prod2',3,6.5

    UNION

    SELECT 'Prod1',3,2.5

    I want to update only the first row(or one row for each product) in the #Products table for each product name.The following command updates all the records.

    Update #Products

    SET Price = PR

    FROM

    (#Products

    INNER JOIN #PRICE ON #Products.Product_Name = #PrICE.Product_Name )

    Please help on this issue.

  • A method needs to be found to identify which row to update. This method has an ID field that uses MIN. A more common strategy would use a date (or such).

    CREATE TABLE #Products

    (

    ProductsID INT IDENTITY, -- NEW

    Product_Name VARCHAR(20),

    Qty INT,

    Price DECIMAL(10,2)

    )

    CREATE TABLE #PrICE

    (

    Product_Name VARCHAR(20),

    Pr DECIMAL(10,2)

    )

    INSERT INTO #PrICE SELECT 'Prod1',2

    INSERT INTO #PrICE SELECT 'Prod2',4

    INSERT INTO #Products

    SELECT 'Prod1',2,2.5 UNION

    SELECT 'Prod2',3,6.5 UNION

    SELECT 'Prod1',3,2.5

    --I want to update only the first row(or one row for each product) in the #Products table for each product name.The following command updates all the records.

    Update #Products

    SET Price = PR

    FROM

    (SELECT MIN(prd.ProductsID) productsID, prd.Product_Name

    from #Products prd

    GROUP BY prd.Product_Name

    ) minPrd

    INNER JOIN #PRICE ON minPrd.Product_Name = #PrICE.Product_Name

    WHERE #Products.productsID = minPrd.productsID

  • How are you defining "first row"? There's nothing in the table that indicates the sequence.

    If you want to update only one row, and don't care which one, or have some column that indicates which one is "first", then it's easy to set up an update based on a row number. That'll only update one row (or however many you want).

    So, which one is "first"?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thx guys,first problem solved but Things got a little more complicated. I want to include a case when clause with the sum function in the Set statement as follows (disregard the field names):

    UPDATE p

    SET p.Price =(CASE WHEN SRND01 <> 0 AND Month('2010.01.28') = 1 THEN SUM(SRBP01)/SUM(SRND01)*C8SPT/C8PWD END )

    FROM(SELECT ROW_NUMBER() OVER(PARTITION BY Product_Name ORDER BY Qty) AS Seq,Price,Product_Name FROM #Products)p

    JOIN #Price pr

    ON pr.Product_Name = p.Product_Name

    AND p.Seq=1

    But I am having an error when trying to run it. Any help please?

  • Turn the aggregation query into either a correlated subquery or a CTE, then use that for your update command.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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