a newbie question
Posted by:
heguangm
Date: May 15, 2005 08:15PM
I put scripts of creating a stored procedure in a file and source it.
I got following errors:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that c
s to your MySQL server version for the right syntax to use near 'update scores, c
et scores.score = (scores.ecols * criteria.ecolc +
' at line 3
Query OK, 2 rows affected, 3 warnings (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 3
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 2
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that c
s to your MySQL server version for the right syntax to use near 'END' at line 1
If I ran the code line by line, it was fine. But when I ran the procedure, the system always say line 1 with error.
Here is the script:
delimiter //
drop procedure if exists update_decision;
create procedure sp_update_decision()
BEGIN
update scores, criteria set scores.score = (scores.ecols * criteria.ecolc +
scores.econs * criteria.econc +
scores.socs * criteria.socc) / 100
where scores.userid = criteria.userid;
create temporary table tst
select scnrid, AVG(score) as ms, MIN(score) as mins, MAX(score) as maxs, STDDEV(score) as stds, COUNT(score) as ns
FROM scores group by scnrid;
update scenarios s1, tst s2 set s1.score = s2.ms, s1.minscore = s2.mins, s1.maxscore = s2.maxs,
s1.varscore = s2.stds, s1.voters = s2.ns
WHERE s1.id = s2.scnrid;
drop temporary table if exists tst;
commit;
END
//
delimiter ;
Someone helps me! Please,
Guangming