MySQL several queries using if

Question

I am right now writing a few MySQL commands, which allow me to generate better statistics. My problem is now that under special circumstances a few queries should be called and under other circumstances not.

my current attempt: (I shorted it)

SET @var1=0, @var2=0;
SELECT @var1 := `id` FROM `sg_playerstats` ORDER BY `id` ASC LIMIT 0, 1;
SELECT @var2 := `Last Updated` FROM `sg_playerranking` LIMIT 0, 1;
IF @var1 > @var2 THEN (

TRUNCATE `sg_playerranking`;

SET
    @Rank := 0;

INSERT INTO
    `sg_playerranking`
SELECT
    @Rank := @Rank + 1 AS `Rank`,
    t.*,
    @var1 AS `Last Updated`
FROM
    (
        SELECT
            `player` AS `Player`,
        FROM
            `sg_playerstats`
        GROUP BY
            `player`
        ORDER BY
            `Score` DESC, ...
    ) t;  )
END IF

SELECT `Rank`, ... FROM `sg_playerranking` WHERE 1

The If statement is not working (Syntax error. Every single query part itself is working!) I did some research but could not find anything but that!

The parentheses make no difference!

EDIT:

Now i am really confused!

My code is:

CREATE PROCEDURE generate_statistics (OUT param1 INT)
BEGIN

    SET @var1=0, @var2=0;
    SELECT @var1 := `id` FROM `sg_playerstats` ORDER BY `id` ASC LIMIT 0, 1;
    SELECT @var2 := `Last Updated` FROM `sg_playerranking` LIMIT 0, 1;
    IF @var1 > @var2 THEN

    TRUNCATE `sg_playerranking`;

    SET
        @Rank := 0;

    INSERT INTO
        `sg_playerranking`
    SELECT
        @Rank := @Rank + 1 AS `Rank`,
        t.*,
        @var1 AS `Last Updated`
    FROM
        (
            SELECT
                `player` AS `Player`,
                SUM(1) AS `Games`,
                SUM(`points`) AS `Points`,
                SUM(`points`) / SUM(1) AS `Points per Game`,
                SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
                SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
                SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
                SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
                SUM(`kills`) AS `Kills`,
                SUM(`kills`) / SUM(1) AS `Kills per Game`,
                SUM(`death`) AS `Deaths`,
                SUM(`death`) / SUM(1) AS `Deaths per Game`,
                SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
                ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
            FROM
                `sg_playerstats`
            GROUP BY
                `player`
            ORDER BY
                `Score` DESC,
                `Points per Game` DESC,
                `Wins per Game` DESC,
                `Loses per Game` ASC,
                `Kills per Game` DESC,
                `Deaths per Game` ASC,
                `Points` DESC,
                `Wins` DESC,
                `Loses` ASC,
                `Kills` DESC,
                `Deaths` ASC,
                `Games` DESC,
                `Time played` DESC,
                `Player` DESC
        ) t;
    END IF;

    SELECT `Rank`, `Player`, `Games`, `Points`, `Points per Game`, `Wins`, `Wins per Game`, `Loses`, `Loses per Game`, `Kills`, `Kills per Game`, `Deaths`, `Deaths per Game`, `Time played`, `Score` INTO param1 FROM `sg_playerranking` WHERE 1
END;

This is the error i get:

#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 '' at line 4

Well it makes no difference if I take DECLARE!

(Sorry for the long query!)

EDIT2:

I finally solved it! This code is working!

DELIMITER //

CREATE PROCEDURE gamestats ()
BEGIN
    SET
        @var1 = 1,
        @var2 = 0;

    SELECT
        @var1 := `id`
    FROM
        `sg_playerstats`
    ORDER BY
        `id` DESC
    LIMIT
        0,
        1;

    SELECT
        @var2 := `Last Updated`
    FROM
        `sg_playerranking`
    LIMIT
        0,
        1;

    IF
        @var1 > @var2
    THEN
        TRUNCATE
            `sg_playerranking`;
        SET
            @Rank := 0;
        INSERT INTO
            `sg_playerranking`
        SELECT
            @Rank := @Rank + 1 AS `Rank`,
            t.*,
            @var1 AS `Last Updated`
        FROM
            (
                SELECT
                    `player` AS `Player`,
                    SUM(1) AS `Games`,
                    SUM(`points`) AS `Points`,
                    SUM(`points`) / SUM(1) AS `Points per Game`,
                    SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
                    SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
                    SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
                    SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
                    SUM(`kills`) AS `Kills`,
                    SUM(`kills`) / SUM(1) AS `Kills per Game`,
                    SUM(`death`) AS `Deaths`,
                    SUM(`death`) / SUM(1) AS `Deaths per Game`,
                    SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
                    ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
                FROM
                    `sg_playerstats`
                GROUP BY
                    `player`
                ORDER BY
                    `Score` DESC,
                    `Points per Game` DESC,
                    `Wins per Game` DESC,
                    `Loses per Game` ASC,
                    `Kills per Game` DESC,
                    `Deaths per Game` ASC,
                    `Points` DESC,
                    `Wins` DESC,
                    `Loses` ASC,
                    `Kills` DESC,
                    `Deaths` ASC,
                    `Games` DESC,
                    `Time played` DESC,
                    `Player` DESC
            ) t;
    END IF;
END;
//
DELIMITER ;

What? happens when you do use DECLARE statements?

DWright
– DWright

2013-01-21 07:16:58 +00:00
Commented Jan 21, 2013 at 7:16 — DWright
– DWright, Commented Jan 21, 2013 at 7:16
Sweet, glad you got it working.

DWright
– DWright

2013-01-21 15:13:28 +00:00
Commented Jan 21, 2013 at 15:13 — DWright
– DWright, Commented Jan 21, 2013 at 15:13

DWright · Accepted Answer · 2013-01-21 06:21:15Z

1

In Mysql, the if statement is only applicable to stored procedures. It is not available in regular sql batches.

answered Jan 21, 2013 at 6:21

DWright

9,5594 gold badges39 silver badges57 bronze badges

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

5 Comments

Sahal Over a year ago

The above question is not a Procedure ?

BrainStone Over a year ago

How do i make it a procedure? And how do I call it then?

DWright Over a year ago

Take a look at: dev.mysql.com/doc/refman/5.0/en/create-procedure.html. If you scroll down, you can see examples of stored procedures.

DWright Over a year ago

Also, look at dev.mysql.com/doc/refman/5.0/en/call.html for how to call as stored procedure. Scrolling down will also show some examples.

BrainStone Over a year ago

I tried and got an really confusing error. Please check the start post!

Collectives™ on Stack Overflow

MySQL several queries using if

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related