|
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,<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
|
|
|
|
|
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
|
|
|
|
|
The typeface of the font used in your name is annoying.
Bastard Programmer from Hell 
|
|
|
|
|
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
|
|
|
|
|
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.
modified 2-Dec-11 13:59pm.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
HI Jorgen,
I think this is not the cause for error. since it only not include the country in Grouping.
|
|
|
|
|
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[^].
|
|
|
|
|
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.
|
|
|
|
|
Difference between varchar and Nvarchar?
|
|
|
|
|
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...
|
|
|
|
|
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.
|
|
|
|
|
|
N
Bastard Programmer from Hell
|
|
|
|
|
That's not the difference. N is what you get when you divide NVarchar by Varchar.
|
|
|
|
|
The difference is: varchar - Nvarchar
|
|
|
|
|
I m facing some problem in execution of sqlite's fail algorithm. Its definition says that it don't rollback changes made prior to encountering constraint voilation and changes to that row where constraint voilation is occured and beyond never occur. I executed it. But when I update values after the row which is encountering constraint voilation,it do that. But definition is saying different thing. What can be the problem. Please tell by an example that how it is executed. Thanks.
|
|
|
|
|
|
I have been working on this one for week and I need help. It is complicated but I will try to explain what is going on and show a little code. What I have is a Silverlight application that communicates via WCF to the database. Now, the complicated part is that a windows job sends a UDP alert packet to each of the Silverlight clients to trigger an update of a DataGrid on the main page which does it update via the same WCF service Async call. This means that if I have 50 Silverlight clients when the alert packet is fired each of the 50 clients will make the same WCF call to the database. I have checked and insured that each client receives the UDP call. The problem is that when the clients all make the same call It gives me a wcf service error on some, but not all, of the clients. The code below shows the basic concept of the code in WCF service database call and Silverlight code. Of course the code is broken up into several sections, but you should get the main idea.
(WCF CODE)
List<customer> clist = new List<customer>();
...
_sqlcommand = new SQLCommand();
_sqlcommand = new SQLCommand();
_sqlcommand.Connection = _database.connectionString;
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.CommandText=”GetCustomers”;
try
{
Customer customer;
_reader = _sqlcommand.ExecuteReader();
While (_reader.Read())
{
customer = new Customer();
customer.Name = _reader[“Name”].ToString();
…
…
…
clist.Add(customer);
}
}
catch (exception e)
{
…
}
}
return clist;
(SILVERLIGHT CODE)
observableCollection<customer> obsCollection = new observableCollection<customer>();
private getmethod()
{
current.client.GetCustomerServiceCompleted += new EventHandler(ServiceReference.GetCustomerServiceCompleteEventArgs(client_GetCustomerServiceCompleted);
current.client.GetCustomerServiceAsync();
}
void client_GetCustomerServiceCompleted (sender, ServiceReference. GetCustomerServiceCompleteEventArgs e)
{
obsCollection = e.Results;
this.CustomerGrid.ItemsSource = obsCollection;
}
Thank you very much!
Steve Holdorf
|
|
|
|
|
Just a suggestion. If you're using Windows XP and Http Binding, XP has a limitation of 10 concurrent Http connections.
|
|
|
|
|
Thanks for the quick response; however, we are using Windows 7 all the way around. Still not sure. My DBA used the NO LOCK for the stored procedure but still no luck.
Thanks,
Steve Holdorf
|
|
|
|
|
To solve the problem all I did was put a using(connection = new SQLConnection ...) statment around the code using the sql connection so the unmanaged resources were garbage collected.
Thanks,
Steve Holdorf
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
