Database

Re: Update Multiple Rows of one table on the base of multiple rows in another table

2-Dec-11 0:26

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

modified 3-Dec-11 6:52am.

Varsha Ramnani2-Dec-11 0:44

Re: Update Multiple Rows of one table on the base of multiple rows in another table

2-Dec-11 0:44

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

modified 4-Dec-11 23:11pm.

Rupesh Kumar Swami2-Dec-11 2:51

Re: Update Multiple Rows of one tableon the base of multiple rows in another table

2-Dec-11 2:51

Hi Varsha,
i tried as following

SQL

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.Country

but 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

Varsha Ramnani2-Dec-11 4:20

Re: Update Multiple Rows of one tableon the base of multiple rows in another table

2-Dec-11 4:20

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

Rupesh Kumar Swami2-Dec-11 20:35

Re: Update Multiple Rows of one tableon the base of multiple rows in another table

2-Dec-11 20:35

Hi Varsha,
i update the query as you suggested. and following is the original query

UPDATE issdetails_new a,<br />
(<br />
SELECT secid, country, PriceDate,<br />
OPEN , High, Low, Close, Ask, midval, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades,<br />
COMMENT , LocalCode<br />
FROM hist_prices<br />
WHERE MktCloseDate = (<br />
SELECT max( MktCloseDate )<br />
FROM hist_prices )<br />
) AS b<br />
SET a.PriceDate = b.PriceDate,<br />
a.Open = b.Open,<br />
a.High = b.High,<br />
a.Low = b.Low,<br />
a.Close = b.Close,<br />
a.Midval = b.Midval,<br />
a.Ask = b.Ask,<br />
a.Last = b.Last,<br />
a.Bid = b.Bid,<br />
a.BidSize = b.BidSize,<br />
a.AskSize = b.AskSize,<br />
a.TradedVolume = b.TradedVolume,<br />
a.MktCloseDate = b.MktCloseDate,<br />
a.Volflag = b.Volflag,<br />
a.TradedValue = b.TradedValue,<br />
a.TotalTrades = b.TotalTrades,<br />
a.Comment = b.Comment,<br />
a.LocalCode = b.LocalCode WHERE a.SecId = b.SecId AND a.Country = b.Country

Its 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 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

Varsha Ramnani2-Dec-11 23:34

Re: Update Multiple Rows of one tableon the base of multiple rows in another table

2-Dec-11 23:34

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

Rupesh Kumar Swami3-Dec-11 0:52

Re: Update Multiple Rows of one table on the base of multiple rows in another table

3-Dec-11 0:52

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

Eddy Vluggen3-Dec-11 4:26

Eddy Vluggen

3-Dec-11 4:26

The typeface of the font used in your name is annoying.

Bastard Programmer from Hell Suspicious | :suss:

Re: Update Multiple Rows of one table on the base of multiple rows in another table

Varsha Ramnani4-Dec-11 17:15

Re: Update Multiple Rows of one table on the base of multiple rows in another table

4-Dec-11 17:15

Sorry... I changed it Smile | :)

Thanks for the suggestion.

"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown

Jörgen Andersson2-Dec-11 3:59

Jörgen Andersson

2-Dec-11 3:59

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...

SQL

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.

SQL

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

modified 2-Dec-11 13:59pm.

Re: Update Multiple Rows of one table on the base of multiple rows in another table

Rupesh Kumar Swami2-Dec-11 20:42

Re: Update Multiple Rows of one table on the base of multiple rows in another table

2-Dec-11 20:42

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,<br />
OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades,<br />
COMMENT , LocalCode<br />
FROM hist_prices<br />
GROUP BY secid, PriceDate,<br />
OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades,<br />
COMMENT , LocalCode ) AS h ON (lp.Secid = h.Secid and lp.country=h.country)<br />
WHEN MATCHED<br />
THEN UPDATE SET lp.PriceDate = h.PriceDate,<br />
lp.open = h.open,<br />
lp.Low = h.Low,<br />
lp.high = h.high,<br />
lp.close = h.close,<br />
lp.ask = h.ask,<br />
lp.last = h.last,<br />
lp.bid = h.bid,<br />
lp.bidsize = h.bidsize,<br />
lp.asksize = h.asksize,<br />
lp.TradedVolume = h.TradedVolume,<br />
lp.MktCloseDate = h.MktCloseDate,<br />
lp.Volflag = h.Volflag,<br />
lp.TradedValue = h.TradedValue,<br />
lp.TotalTrades = h.TotalTrades,<br />
lp.Comment = h.Comment,<br />
lp.LocalCode = h.LocalCode

but 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

Jörgen Andersson2-Dec-11 22:45

Jörgen Andersson

2-Dec-11 22:45

That's not a very descriptive error message. And my experience with mysql is none.

But I can see one error in the USING query, you have "Country" in the SELECT but not in the GROUP BY.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: Update Multiple Rows of one table on the base of multiple rows in another table

Rupesh Kumar Swami2-Dec-11 23:32

Re: Update Multiple Rows of one table on the base of multiple rows in another table

2-Dec-11 23:32

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

Jörgen Andersson3-Dec-11 3:32

Jörgen Andersson

3-Dec-11 3:32

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

Re: Update Multiple Rows of one table on the base of multiple rows in another table

Rupesh Kumar Swami3-Dec-11 1:18

3-Dec-11 1:18

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

Sqlserver

infobeena1-Dec-11 22:28

infobeena

1-Dec-11 22:28

Difference between varchar and Nvarchar?

infobeena1-Dec-11 22:33

infobeena

1-Dec-11 22:33

Tags : Varchar ,NVarchar ,Varchar(n),NVarchar(n),SQL Server,String,Length,Difference.

Hi Friends,in this post i would like to explain difference between Varchar & NVarchar in SQL Server.

* The data type Varchar and NVarchar are the sql server data types, both will used to store the string values.

Differences :

1 Character Data Type

Varchar - Non-Unicode Data
NVarchar - Unicode Data

2 Character Size

Varchar - 1 byte
NVarchar - 2 bytes

3 Maximum Length

Varchar - 8,000 bytes
NVarchar - 4,000 bytes

4 Storage Size

Varchar - Actual Length (in bytes)
NVarchar - 2 times Actual Length (in bytes)

* The abbreviation for Varchar is Variable Length character String.

* The abbreviation of NVarchar is uNicode Variable Length character String.

Thank You...

jschell2-Dec-11 8:49

jschell

2-Dec-11 8:49

infobeena wrote:
3 Maximum Length

Varchar - 8,000 bytes

NVarchar - 4,000 bytes

Wrong.

It is characters not bytes.

And both types in newer versions allow for a size of 'max' which allow for very large sizes.

thatraja1-Dec-11 22:41

thatraja

1-Dec-11 22:41

Frankly Google is more than enough for this kind of simple questions. So always search in web before asking questions in forums.
What is the difference between varchar and nvarchar?[^]

thatraja

My Dad had a Heart Attack on this day so don't...
Pompeyboy3 here | Nobody remains a virgin, Life screws everyone Sigh | :sigh:

Eddy Vluggen2-Dec-11 7:31

Eddy Vluggen

2-Dec-11 7:31

Bastard Programmer from Hell Suspicious | :suss: