CODEWITHSUNDEEP
sqlsql-server
isic5
isic5

Reputation: 191

Update table with data from select query including second table

The goal of this query is to update the columns MinPrice and MaxPrice in Table 1 with Max and Min prices from Table2 where CountryCode and ProductCode are matching.

When I run this query the whole columns of Max and Minprice in Table 1 fill with the first value that comes up from the select query.

If I run the select query by itself it shows the correct min and max values per Country per Product.

   UPDATE Table1
        SET MinPrice = MinOfPrice, Maxprice = MaxOfPrice
        FROM (SELECT Min(lp.Price) AS MinOfPrice, Max(lp.Price) AS MaxOfPrice 
        FROM Table2 lp INNER JOIN Table1 d ON lp.CountryCode = d.CountryCode AND 
        lp.ProductCode = d.ProductCode
        GROUP BY lp.CountryCode, lp.ProductCode, lp.PriceOriginTypeCode) h ;

Upvotes: 0

Views: 86

Answers (6)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

You have to bring Table1 out your subquery. And I don't understand why you group by three columns - this way you can have several rows for one CountryCode-ProductCode combination.

UPDATE t
SET MinPrice = h.MinOfPrice, Maxprice = h.MaxOfPrice
FROM Table1 t
    JOIN (
        SELECT  lp.CountryCode,
             lp.ProductCode,
             Min(lp.Price) AS MinOfPrice,
             Max(lp.Price) AS MaxOfPrice 
        FROM Table2 lp
        GROUP BY lp.CountryCode, lp.ProductCode
        , lp.PriceOriginTypeCode -- why do you also group by this column?
    ) h ON h.CountryCode = t.CountryCode AND h.ProductCode = t.ProductCode;

Upvotes: 0

HJB
HJB

Reputation: 1

Try this:

UPDATE Table1
SET MinPrice = h.MinOfPrice
,Maxprice = h.MaxOfPrice
FROM (
      SELECT d.CountryCode, d.ProductCode,
             Min(lp.Price) AS MinOfPrice,
             Max(lp.Price) AS MaxOfPrice 
      FROM Table 2 lp 
      INNER JOIN Table1 d 
         ON lp.CountryCode = d.CountryCode
         AND lp.ProductCode = d.ProductCode
      GROUP BY d.CountryCode, d.ProductCode
) h ;

I have got rid of lp.PriceOriginTypeCode as I don't know if this is part of TABLE1 too

Upvotes: 0

ser_nicky
ser_nicky

Reputation: 356

You should get the aggregated values within the subquery and then do the join. Something like this:

update t1
set t1.minPrice = t2.minPrice
    ,t1.maxPrice = t2.maxPrice
from table1 t1
         inner join (
                    select min(price) minPrice
                          ,max(price) maxPrice
                          ,productCode
                          ,countryCode
                    from table2
                    group by productCode
                             ,countryCode
                     ) t2 on t2.productCode = t.productCode
                           and t2.countryCode = t.countryCode

Upvotes: 0

Himanshu
Himanshu

Reputation: 3970

I guess your current query is modifying each and every record's min and max of table1 try the below to update via common columns 

   UPDATE Table1
    SET MinPrice = MinOfPrice, Maxprice 
   = MaxOfPrice
      FROM (SELECT Min(lp.Price) AS 
      MinOfPrice, Max(lp.Price) AS 
     MaxOfPrice 
    FROM Table 2 lp INNER JOIN Table1 d 
   ON lp.CountryCode = d.CountryCode 
  AND 
      lp.ProductCode = d.ProductCode
    GROUP BY lp.CountryCode, 
    lp.ProductCode, 
    lp.PriceOriginTypeCode) h
      where h.some_common_column =
             Table1.common_column
        -- make this query as corelated via above where clause

Upvotes: 1

vitalygolub
vitalygolub

Reputation: 735

Just to explain my comment

UPDATE Table1
        SET MinPrice = MinOfPrice, Maxprice = MaxOfPrice
FROM (SELECT Min(lp.Price) AS MinOfPrice, Max(lp.Price) AS MaxOfPrice, lp.CountryCode,lp.ProductCode
        FROM Table2 lp INNER JOIN Table1 d ON lp.CountryCode = d.CountryCode AND 
        lp.ProductCode = d.ProductCode
        GROUP BY lp.CountryCode, lp.ProductCode, lp.PriceOriginTypeCode) h join Table1 on Table1.ProductCode=h.ProductCode and Table1.CountryCode=h.CountryCode

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

I think this would be your solution or almost similar to your solution.

    UPDATE Table1 
            SET table1.MinPrice = lp.MinOfPrice, table1.Maxprice = lp.MaxOfPrice
            FROM Table1 INNER JOIN ( SELECT top 100 percent  CountryCode, ProductCode, MAX(Price) as MaxOfPrice, MIN(Price) as MinOfPrice FROM Table1 group by  CountryCode, ProductCode) as lp ON lp.CountryCode = Table1.CountryCode AND 
            lp.ProductCode = Table1.ProductCode

Upvotes: 1

Related Questions