Can anybody tell me what's wrong with this query?

CREATE PROCEDURE Click(
    in  ipaddress varchar(45))
BEGIN
    DECLARE ex int;
    SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
IF ex = 0 THEN
    INSERT INTO clickactivity (IP, Clicks) VALUES(ipaddress, 0);
END IF;   
    UPDATE clickactivity SET Clicks = Clicks + 1;
END

It complains about line 4 like so:

#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

There are two things wrong

First, use DELIMITER $$ so semicolons are interpreted inside the stored procedure and not the command line. After defining the stored procedure, switch the delimiter back to semicolon (;).

Next, UPDATE clickactivity SET Clicks = Clicks + 1; will update every row and you don't want that.

SUGGESTION #1

Add WHERE IP = ipaddress; to the UPDATE clickactivity

DELIMITER $
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
    DECLARE ex int;
    SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
    IF ex = 0 THEN
        INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 0);
    END IF;   
    UPDATE clickactivity SET Clicks = Clicks + 1 WHERE IP = ipaddress;
END $
DELIMITER ;

SUGGESTION #2

Why do two or three queries to register a single click ?

Change the code to do the query so that it INSERTs and UPDATEs at the same time

DELIMITER $
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
    INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
    ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;
END $
DELIMITER ;

In that event, you don't need a stored procedure. Just do the INSERT

INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;

Of course, doing this works if ipaddress is the PRIMARY KEY or is a UNIQUE KEY in clickactivity.

GIVE IT A TRY !!!

StackExchange Database Administrators Q#83183, answer score: 3