Solved:Update Multiple Rows of one table on the base of multiple rows in another table
-
Hi All, I want to update multiple columns of multiple rows of one table. Single/Multiple column update for single/Multiple row is quite easy but i want to update the rows of one table on the basis of rows of other table. Here I demonstrate that what i want with single query Table1 - History Fields and Data Date SId Country Price High Low ........ 1 Dec 1 US 2 3 1 values... 1 Dec 2 US 3 4 2 values... 1 Dec 3 US 4 5 3 values... 1 Dec 4 US 5 6 4 values... 2 Dec 1 US 7 8 5 values... 2 Dec 2 US 8 9 6 values... 2 Dec 3 US 9 10 7 values... 2 Dec 4 US 10 11 8 values... Table2 _ LatestPrice Fields and Data SId Country Price High Low ........ 1 US 1 2 1 values... 2 US 1 2 1 values... 3 US 1 2 1 values... 4 US 1 2 1 values... I want to update the LatestPrice table data on the base of field SID and Country from History table data.Here i pick the only highest date data from History table. Now i want to update data in LatestPrice table. so there are two approaches One way is the pick one by one record from resultset and update the LatestPrice on the base of field SID and Country. so there are mulitple queries depends on the no. of records in resultset Other way is write singe query in this way that they find automatically SId and Country and update the data. So i was unable to write single query for update the Data. Please suggest that how can i do this taks any help will be appreciated
rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
I'll probably take some heat for suggesting a
Merge, but as I assume there might be new SIds and countries appearing in the history table...MERGE INTO LatestPrice lp
USING (
SELECT Max(DATE),SId,Country,Price,High,Low
FROM history h
GROUP BY SId,Country,Price,High,Low
ON lp.SId = h.SId AND lp.Country = h.Country
WHEN MATCHED THEN UPDATE
SET lp.Price = h.Price
,lp.High = h.High
,lp.Low = h.Low
WHEN NOT MATCHED THEN
INSERT (lp.sid,lp.Country,lp.Price,lp.High,lp.Low)
VALUES (h.SId,h.Country,h.Price,h.High,h.Low)Another approach is ofcourse to not have a separate table for the LatestPrice, but a view. Which of course might have performance issues.
CREATE OR REPLACE VIEW LatestPrice (lastdate,SId,Country,Price,High,Low)
AS (
SELECT Max(DATE) lastdate,SId,Country,Price,High,Low
FROM history h
GROUP BY SId,Country,Price,High,Low
)Edit: changed the view.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Hi Varsha, i tried as following
UPDATE LatestPrice SET price = LatestPricesFromHistory.price,
FROM ( SELECT *
FROM history
WHERE Date1 = (
SELECT max( Date1 )
FROM history ) ) AS LatestPricesFromHistory
WHERE LatestPrice.Sid = LatestPricesFromHistory.SId and LatestPrice.Country = LatestPricesFromHistory.Countrybut it give me following error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From { select * from history where Date1=(select max(Date1) from history' at line 1. Please suggest
rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
I think MySQL does not support Update-Select. I have used this syntax in SQL server 2005. try this one for MySQL :
Update LatestPrice,
( Your Query for finding Latest Prices goes here) as LatestPriceFromHistory
Set price = LatestPriceFromHistory.price
where LatestPrice.SID=LatestPriceFromHistory.SID and LatestPrice.Country = LatestPriceFromHistory.Country"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
-
I think MySQL does not support Update-Select. I have used this syntax in SQL server 2005. try this one for MySQL :
Update LatestPrice,
( Your Query for finding Latest Prices goes here) as LatestPriceFromHistory
Set price = LatestPriceFromHistory.price
where LatestPrice.SID=LatestPriceFromHistory.SID and LatestPrice.Country = LatestPriceFromHistory.Country"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
Hi Varsha, i update the query as you suggested. and following is the original query
UPDATE issdetails_new a, ( SELECT secid, country, PriceDate, OPEN , High, Low, Close, Ask, midval, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode FROM hist_prices WHERE MktCloseDate = ( SELECT max( MktCloseDate ) FROM hist_prices ) ) AS b SET a.PriceDate = b.PriceDate, a.Open = b.Open, a.High = b.High, a.Low = b.Low, a.Close = b.Close, a.Midval = b.Midval, a.Ask = b.Ask, a.Last = b.Last, a.Bid = b.Bid, a.BidSize = b.BidSize, a.AskSize = b.AskSize, a.TradedVolume = b.TradedVolume, a.MktCloseDate = b.MktCloseDate, a.Volflag = b.Volflag, a.TradedValue = b.TradedValue, a.TotalTrades = b.TotalTrades, a.Comment = b.Comment, a.LocalCode = b.LocalCode WHERE a.SecId = b.SecId AND a.Country = b.CountryIts works well. Thanks a lot but there are one more problem in table issdetails, there are 9500 records and in table hist_prices there are 41,50,000 records so it takes 11 -13 minute for update the table issdetails. Any option to reduce the execution time ? Please suggestrup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
I'll probably take some heat for suggesting a
Merge, but as I assume there might be new SIds and countries appearing in the history table...MERGE INTO LatestPrice lp
USING (
SELECT Max(DATE),SId,Country,Price,High,Low
FROM history h
GROUP BY SId,Country,Price,High,Low
ON lp.SId = h.SId AND lp.Country = h.Country
WHEN MATCHED THEN UPDATE
SET lp.Price = h.Price
,lp.High = h.High
,lp.Low = h.Low
WHEN NOT MATCHED THEN
INSERT (lp.sid,lp.Country,lp.Price,lp.High,lp.Low)
VALUES (h.SId,h.Country,h.Price,h.High,h.Low)Another approach is ofcourse to not have a separate table for the LatestPrice, but a view. Which of course might have performance issues.
CREATE OR REPLACE VIEW LatestPrice (lastdate,SId,Country,Price,High,Low)
AS (
SELECT Max(DATE) lastdate,SId,Country,Price,High,Low
FROM history h
GROUP BY SId,Country,Price,High,Low
)Edit: changed the view.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
HI Jorgen thanks a lot for you suggestion. View option is not applicable for my requirement. since I need to update the data in table. Also i tried the Merge option as you suggested but with a little modification and it is the original query
MERGE INTO issdetails_new lp USING ( SELECT Max( MktCloseDate ) , secid,country, PriceDate, OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode FROM hist_prices GROUP BY secid, PriceDate, OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode ) AS h ON (lp.Secid = h.Secid and lp.country=h.country) WHEN MATCHED THEN UPDATE SET lp.PriceDate = h.PriceDate, lp.open = h.open, lp.Low = h.Low, lp.high = h.high, lp.close = h.close, lp.ask = h.ask, lp.last = h.last, lp.bid = h.bid, lp.bidsize = h.bidsize, lp.asksize = h.asksize, lp.TradedVolume = h.TradedVolume, lp.MktCloseDate = h.MktCloseDate, lp.Volflag = h.Volflag, lp.TradedValue = h.TradedValue, lp.TotalTrades = h.TotalTrades, lp.Comment = h.Comment, lp.LocalCode = h.LocalCodebut it gives me following error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MERGE INTO issdetails_new lp USING ( SELECT Max(MktCloseDate),secid,Pric' at line 1 Please suggest where i am doing wrong.rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
HI Jorgen thanks a lot for you suggestion. View option is not applicable for my requirement. since I need to update the data in table. Also i tried the Merge option as you suggested but with a little modification and it is the original query
MERGE INTO issdetails_new lp USING ( SELECT Max( MktCloseDate ) , secid,country, PriceDate, OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode FROM hist_prices GROUP BY secid, PriceDate, OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode ) AS h ON (lp.Secid = h.Secid and lp.country=h.country) WHEN MATCHED THEN UPDATE SET lp.PriceDate = h.PriceDate, lp.open = h.open, lp.Low = h.Low, lp.high = h.high, lp.close = h.close, lp.ask = h.ask, lp.last = h.last, lp.bid = h.bid, lp.bidsize = h.bidsize, lp.asksize = h.asksize, lp.TradedVolume = h.TradedVolume, lp.MktCloseDate = h.MktCloseDate, lp.Volflag = h.Volflag, lp.TradedValue = h.TradedValue, lp.TotalTrades = h.TotalTrades, lp.Comment = h.Comment, lp.LocalCode = h.LocalCodebut it gives me following error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MERGE INTO issdetails_new lp USING ( SELECT Max(MktCloseDate),secid,Pric' at line 1 Please suggest where i am doing wrong.rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
That's not a very descriptive error message. And my experience with mysql is none. But I can see one error in the
USINGquery, you have "Country" in theSELECTbut not in theGROUP BY.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
That's not a very descriptive error message. And my experience with mysql is none. But I can see one error in the
USINGquery, you have "Country" in theSELECTbut not in theGROUP BY.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
HI Jorgen, I think this is not the cause for error. since it only not include the country in Grouping.
rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
Hi Varsha, i update the query as you suggested. and following is the original query
UPDATE issdetails_new a, ( SELECT secid, country, PriceDate, OPEN , High, Low, Close, Ask, midval, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades, COMMENT , LocalCode FROM hist_prices WHERE MktCloseDate = ( SELECT max( MktCloseDate ) FROM hist_prices ) ) AS b SET a.PriceDate = b.PriceDate, a.Open = b.Open, a.High = b.High, a.Low = b.Low, a.Close = b.Close, a.Midval = b.Midval, a.Ask = b.Ask, a.Last = b.Last, a.Bid = b.Bid, a.BidSize = b.BidSize, a.AskSize = b.AskSize, a.TradedVolume = b.TradedVolume, a.MktCloseDate = b.MktCloseDate, a.Volflag = b.Volflag, a.TradedValue = b.TradedValue, a.TotalTrades = b.TotalTrades, a.Comment = b.Comment, a.LocalCode = b.LocalCode WHERE a.SecId = b.SecId AND a.Country = b.CountryIts works well. Thanks a lot but there are one more problem in table issdetails, there are 9500 records and in table hist_prices there are 41,50,000 records so it takes 11 -13 minute for update the table issdetails. Any option to reduce the execution time ? Please suggestrup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
Sorry I don't know much about MySQL, but you can google for "Indexing in MySQL".
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
-
Sorry I don't know much about MySQL, but you can google for "Indexing in MySQL".
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
Thanks a ton Varsha Your suggestion works well Now It takes only 8 - 9 seconds
rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
I'll probably take some heat for suggesting a
Merge, but as I assume there might be new SIds and countries appearing in the history table...MERGE INTO LatestPrice lp
USING (
SELECT Max(DATE),SId,Country,Price,High,Low
FROM history h
GROUP BY SId,Country,Price,High,Low
ON lp.SId = h.SId AND lp.Country = h.Country
WHEN MATCHED THEN UPDATE
SET lp.Price = h.Price
,lp.High = h.High
,lp.Low = h.Low
WHEN NOT MATCHED THEN
INSERT (lp.sid,lp.Country,lp.Price,lp.High,lp.Low)
VALUES (h.SId,h.Country,h.Price,h.High,h.Low)Another approach is ofcourse to not have a separate table for the LatestPrice, but a view. Which of course might have performance issues.
CREATE OR REPLACE VIEW LatestPrice (lastdate,SId,Country,Price,High,Low)
AS (
SELECT Max(DATE) lastdate,SId,Country,Price,High,Low
FROM history h
GROUP BY SId,Country,Price,High,Low
)Edit: changed the view.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
HI Jorgen, My problem is solved. please check this thread. Thanks for suggest new way of update and Insert. I was not aware of this way.
rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
HI Jorgen, I think this is not the cause for error. since it only not include the country in Grouping.
rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
You're right, it seems that the problem is that MySQL isn't supporting the Merge command. But no problem, they have one of their own instead[^].
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
you can try the following syntax
Update LatestPrice set price = LatestPricesFromHistory.price,...other columns From
(
here goes query to find highest date data from History table
) as LatestPricesFromHistory
where LatestPrice.SID = LatestPricesFromHistory.SID and LatestPrice.Country = LatestPricesFromHistory.Country"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
-
The typeface of the font used in your name is annoying.
Bastard Programmer from Hell :suss:
Sorry... I changed it :) Thanks for the suggestion.
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown