MySQL Stored Procedure with Insert Select and user variable

Question

Problem: I have a MySQL stored procedure and I want to reset a user variable to 0 each time the procedure is called, but the variable seems to be remembering its value from each previous run and I cannot initialize it to zero.

Details: I have a data table (called data) which I read from and then generate records in a 1-to-1 correlation in another table (called results). I do this in a stored procedure that utilizes an Insert Select statement. I also use a user variable @mycount which increments with each row. Here is a simplified version of my code:

DELIMITER //
CREATE PROCEDURE doSomething
BEGIN
    SET @mycount := 0;
    INSERT INTO results (data_id, mycounter)
        SELECT data.id, (@mycount := @mycount+1)
        FROM data LEFT JOIN results ON data.id = results.data_id
        WHERE ... GROUP BY ...
        HAVING ...
        ORDER BY ...;
END //
DELIMITER ;

Analysis: This almost works as desired except I need @mycount to reset to zero each time it is run and the SET statement above is not achieving that. When debugging, I can see that the last value of @mycount is always remembered from the previous run of the stored procedure. I've also tried setting it to zero after the insert.

Notes:

I am using a @ variable to increment and keep track of row number. Perhaps there is another option here?
The problem seems related to the Having clause. When I run a similar but different query without the having clause, the variable resets no problem.

Question: Why can't I set @mycount to zero before running each time? If it IS resetting and it's just that my Having clause causes an unexpected count, is there some better way to rewrite my SQL?

I'm hoping someone has run into something similar, since this seems pretty obscure.

possible duplicate of [How to SELECT \* INTO \[temp table\] FROM \[stored procedure\]](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) — Jackcob, Mar 31 '14 at 04:09

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

I think you are using session variable(i.e; @mycount).Check below site you will get an answer

MySQL: @variable vs. variable. Whats the difference?

edited May 23 '17 at 10:24

Community

1
1

answered Aug 11 '13 at 10:54

Ajay

2,022
19
33

score 0 · Answer 2 · answered Aug 10 '13 at 10:54

0

I dont find any reason why your code should not work. Anyways, try the below method.

INSERT INTO results (data_id, mycounter)
select a.id, a.cnt from (
select @mycount := 0 from dual
join ( SELECT data.id, (@mycount := @mycount+1) cnt
        FROM data LEFT JOIN results ON data.id = results.data_id
        WHERE ... GROUP BY ...)) a;

In this case, you dont need to specify SET @mycount := 0;

answered Aug 10 '13 at 10:54

Akhil

2,602
23
36

You can also try something like: `INSERT INTO results (data_id, mycounter) SELECT data.id, (@mycount := @mycount + 1) FROM (SELECT @mycount := 0) der, data LEFT JOIN results ON data.id = results.data_id;` – wchiquito Aug 10 '13 at 11:30

wchiquito · Answer 3 · 2013-08-10T23:13:24.030

0

In the following SQL Fiddle I can not reproduce the problem you pose, in theory works as expected.

UPDATE

Try:

DELIMITER //

CREATE PROCEDURE doSomething()
BEGIN
    SET @mycount := 0;
    INSERT INTO results (data_id, mycounter)
        SELECT der.id, (@mycount := @mycount + 1)
        FROM (
            SELECT data.id
            FROM data
                LEFT JOIN results ON data.id = results.data_id
            WHERE ...
            GROUP BY ...
            HAVING ...
            ORDER BY ...
        ) der;
END //

DELIMITER ;

edited Aug 10 '13 at 23:13

answered Aug 10 '13 at 11:07

wchiquito

16,177
2
34
45

So strange that somehow it retains its value in my code. I've tried the answers so far on this page and in each case, the value persists from one call to the next. I guess there must be some other problem with my procedure. – Dan Aug 10 '13 at 12:30
How do you call the stored procedure? Do you use the MySQL command line or use a particular IDE? – wchiquito Aug 10 '13 at 12:50
I'm calling it in a PHP script. Could it have something to do with the fact that it is a `@` variable? Is there a way to use a non-session variable (without the `@`) in this context? – Dan Aug 10 '13 at 16:50
And failing that, is there any way other than this to increment a variable amidst an insert select? – Dan Aug 10 '13 at 17:19
One option is a cursor, the performance should be evaluated. [13.6.6. Cursors](http://devmysql.photoforerunner.com/doc/refman/5.6/en/cursors.html) – wchiquito Aug 10 '13 at 22:51

MySQL Stored Procedure with Insert Select and user variable

3 Answers3