Solved

MySQL While do Syntax Errors

Posted on 2014-12-09
2
334 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
2 Comments
 
LVL 48

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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