MySQL Forums
Forum List  »  Stored Procedures

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

Options: ReplyQuote


Subject
Views
Written By
Posted
a newbie question
2920
May 15, 2005 08:15PM
2101
May 15, 2005 11:34PM
2001
May 16, 2005 02:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.