MySQL While Loop Error

[supremegood420]

Hello all. I am fairly new to MySQL and so this is probably an easy one for most of you. I have a very simple script I wrote that seems to not want to work even though I have read and re-read the syntax for it several times and have yet to see anything wrong with the statement.

Here is the code in question;
*********************************************************
set @reprice_id := 0;
set @num := 0;

select @reprice_id := new_reprice_id, @num := count(*) from new_ims_reprice group by new_reprice_id having count(*) > 1 limit 1;

while(@num > 0)

delete from new_ims_reprice where new_reprice_id = @reprice_id limit @num - 1;

set @num := 0;

select @reprice_id := new_reprice_id, @num := count(*) from new_ims_reprice group by new_reprice_id having count(*) > 1 limit 1;

end while;
*********************************************************

And this is the error I keep getting;
*********************************************************

Script line: 6 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 'while(@num > 0)

delete from new_ims_reprice where new_reprice_id = @repric' at line 1
*********************************************************

I can't for the life of me figure out why it doesn't like this code. I found a similar issue someone else had but their solution was a wash for me. I am desperate for help with this as this seems like way too simple a task to have this much trouble.

Thank you all in advance for any assistance.

Xander

 

[r937]

i think the error you're getting, which is right at the WHILE keyword, is occurring because the WHIL is not inside a stored proc

perhaps you could explain what your code is doing? maybe it doesn't need a WHILE loop at all...


r937.com | rudy.ca