Update table with data from select query including second table

Question

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 ;

does Table1 has fields to join by CountryCode, ProductCode? Just select lp.CountryCode, lp.ProductCode from table h (include in select list ) and update using from Table1 join h on — vitalygolub
– vitalygolub, Commented Feb 20, 2019 at 10:55
@vitalygolub Yes both tables have the fields CountryCode,ProductCode. I am not sure I 100% understand what you are saying I will try it and include in my edit. — isic5
– isic5, Commented Feb 20, 2019 at 10:58

DarkRob · Accepted Answer · 2019-02-20 10:56:55Z

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

vitalygolub · Accepted Answer · 2019-02-20 11:00:41Z

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

Himanshu · Accepted Answer · 2019-02-20 11:01:16Z

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

ser_nicky · Accepted Answer · 2019-02-20 11:08:02Z

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

HJB · Accepted Answer · 2019-02-20 11:09:15Z

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

SQL Server does not know which row to update unless you tell it. The Join values must also be in the FROM

Denis Rubashkin · Accepted Answer · 2019-02-20 11:15:27Z

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;

Collectives™ on Stack Overflow

Update table with data from select query including second table

6 Answers 6

2 Comments

Comments

Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

1 Comment

Comments

Related