MySQL syntax error | variable = variable + interger

Question

Hi I have written this MySQL stored proc and I keep getting this syntax error

#1064 - 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 '@start := @start + 3600;

END WHILE;

@start = 1505469600;
' at line 42

Here is my stored procedure, I haven't been able to run it yet because of the syntax errors so there might be a few things wrong with it. If you spot any, it would be really helpful if you could help me.

DELIMITER ;;

CREATE PROCEDURE insert_zone_time_range_data()

BEGIN

SET @start = 1505469600, @end = 1505483940;

WHILE @start < @end DO

    WHILE @start < @end DO

        INSERT INTO daily_stats_zone_visitors_time_range
        (
            day_epoch,
            day_of_week,
            time_range_hours,
            venue_id,
            visitors,
            visitors_new
        )
        SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(ts,'%Y-%m-%d')) AS day_epoch,
        FROM_UNIXTIME(ts,'%W') AS day_of_week,
        FROM_UNIXTIME(@start, '%h') + '-' + FROM_UNIXTIME(@end, '%h') AS time_range_hours,
        venue_id,
        COUNT(DISTINCT mac) AS visitors,
        COUNT(id) AS visitors_new
        FROM (              
            SELECT probe_request.venue_id,
            probe_request.mac,
            macs.id,
            probe_request.ts
            FROM probe_request
            LEFT OUTER JOIN daily_stats_venue_mac_cache AS macs
            ON probe_request.mac = macs.mac
            AND probe_request.venue_id = macs.venue_id
            AND macs.first_seen BETWEEN @start AND @end
            WHERE probe_request.ts BETWEEN @start AND @end
            GROUP BY probe_request.venue_id, probe_request.mac
        ) AS temp
        GROUP BY venue_id;

        @start = @start + 3600;

    END WHILE;

    @start = 1505469600;
    @end = @end - 3600;

END WHILE;

END;
;;

I added the DELIMITER ;; to the top of my script because I kept getting a syntax error when I was setting my variables and this seemed to fix it. In all honesty I have no idea why but that bit seems to work now.

I don't have any results to should you because of the error so sorry about that.Thanks for the help in advance it is much appreciated

@Forward I have made that change and I am getting the same error — Luke Rayner
– Luke Rayner, Commented Sep 20, 2017 at 11:16
@fancyPants I have done that and I am getting the same error — Luke Rayner
– Luke Rayner, Commented Sep 20, 2017 at 11:16
The error is just before the fragment of query displayed by MySQL in the error message. — axiac
– axiac, Commented Sep 20, 2017 at 11:17

Bhushan · Accepted Answer · 2017-09-20 11:24:49Z

5

To set value into variable you have to use Set keyword.

Here it will be

   set @start = @start + 3600

answered Sep 20, 2017 at 11:24

Bhushan

6403 silver badges9 bronze badges

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

Comments

axiac · Accepted Answer · 2017-09-20 11:27:25Z

The MySQL documentation about user-defined variables says:

One way to set a user-defined variable is by issuing a SET statement:
SET @var_name = expr [, @var_name = expr] ...
For SET, either = or := can be used as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements:
mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;

The line:

@start = @start + 3600;

is not a valid MySQL statement (and it is not valid even with := instead of =). The correct way to assign a value to the variable @start in this context is to use a SET statement:

SET @start = @start + 3600;

The same for the other two assignments:

SET @start = 1505469600;
SET @end = @end - 3600;

Collectives™ on Stack Overflow

MySQL syntax error | variable = variable + interger

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related