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] — Jackcob
– Jackcob, Commented Mar 31, 2014 at 4:09

Community · Accepted Answer · 2017-05-23 10:24:05Z

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, 2017 at 10:24

CommunityBot

11 silver badge

answered Aug 11, 2013 at 10:54

Ajay

2,08020 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Akhil · Accepted Answer · 2013-08-10 10:54:06Z

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, 2013 at 10:54

Akhil

2,60225 silver badges37 bronze badges

1 Comment

wchiquito Over a year ago

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 · Accepted Answer · 2013-08-10 23:13:24Z

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, 2013 at 23:13

answered Aug 10, 2013 at 11:07

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

5 Comments

Dan Over a year ago

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.

wchiquito Over a year ago

How do you call the stored procedure? Do you use the MySQL command line or use a particular IDE?

Dan Over a year ago

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 Over a year ago

And failing that, is there any way other than this to increment a variable amidst an insert select?

wchiquito Over a year ago

One option is a cursor, the performance should be evaluated. 13.6.6. Cursors

Collectives™ on Stack Overflow

MySQL Stored Procedure with Insert Select and user variable

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related