Solved

MySQL While do Syntax Errors

Posted on 2014-12-09
2
343 Views
Last Modified: 2014-12-10
Good night, i am trying to translate this script into MySQL. In fact, i have made a progress of this little snippet.
Could you please tell me what am i doing wrong?


BEGIN

CREATE TEMPORARY TABLE mydb.VAR001  (
id int,
trainingId int 
);

SET @row_number:=0;
insert into mydb.VAR001 
select @row_number:=@row_number+1 AS row_number, trainingId from mydb.tb_trainingHistoryStatus where userId = 77423 and trainingStatusId = 2 
group by trainingId;

DECLARE MIN INT default 0;
DECLARE MAX INT default 0;


SET MIN := ( SELECT MIN(id) FROM mydb.VAR001 );
SET MAX := ( SELECT MAX(id) FROM mydb.VAR001 );

WHILE ( @MIN < @MAX ) DO
select * from mydb.VAR001
SET @MIN = @MIN+1
END WHILE;



DROP TEMPORARY TABLE mydb.VAR001;

END

Open in new window


1- The tempoarary table could duplicate on execution time because of threads correct?
2- I get an error on the While statement
3- generally i cannot run from BEGIN to END without problems. I get several errors.

Thank you very much for your advice
0
Comment
Question by:JavierVera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40490274
Can you describe what it is you actually want to achieve?

You might not need a temp table at all, but I cannot suggest an alternative without knowing more about the objective.
0
 
LVL 1

Author Comment

by:JavierVera
ID: 40492713
Thanks for your help., i have found the issue. What i wanted is to get somewhat like an anonymous block in order to test the while sentence i am building.
So far, i got to run this script from a Java app. And it've worked nicely.
Apart of this, in the snipped i pasted earlier i find that i missed the DECLARE statement wich is NOT at the begginning of the script and this is a must when building procedures.
thanks for your time.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question