CODEWITHSUNDEEP
mysqlphpmyadminmysql-error-1064
rkcell
rkcell

Reputation: 468

MySQL server doesnt creates a stored procedure via phpmyAdmin

delimiter //
create procedure rankPagesLive()
BEGIN 
    SET @r=0;
    UPDATE pageslive SET Rank= @r:= (@r+1) ORDER BY fan_count DESC;
END //
delimeter ;

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 'delimiter // create procedure rankPagesLive() BEGIN SET @r=0' at line 1

What's wrong?

Upvotes: 1

Views: 1038

Answers (3)

rkcell
rkcell

Reputation: 468

The problem was with phpmyAdmin and not with SQL code, delimiter identifier isn't allowed via SQL query (probably on my server only),

After I logged in to MySQL server by SSH - it works just fine.

Thank you for contribution.

Upvotes: 0

Nicola Cossu
Nicola Cossu

Reputation: 56397

You can use a single query in a way like this

update pageslive as p
inner join (
 select @r:=@r+1 as rn,id
    from pageslive,(select @r:=0) as r order by fan_count desc
     ) as t
 on p.id = t.id
 set rank = rn

but IMHO I would avoid to store a calculated field within the table. You can always get this value with a simple select.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Last line should be:

delimiter ;

Upvotes: 2

Related Questions