debugsqlMinor
Error 1064 - You have an error in your SQL syntax;
Viewed 0 times
erroryouryousql1064syntaxhave
Problem
I am new to developing field, and I have encountered an error while creating a trigger.Could anyone please figure out what is the error?
The error encountered is,
[Err] 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 'COUNT(Id) into count FROM tblcustomfeedback WHERE
feedbackDate = NOW() ORDER BY' at line 11
DELIMITER $
CREATE TRIGGER update_feedback_dashboard
AFTER INSERT
ON tblcustomfeedback FOR EACH ROW
BEGIN
DECLARE averageScore float default 0;
DECLARE avgscore float default 0;
DECLARE IdCount float default 0;
SELECT SUM(avg_score) into avgscore, COUNT(Id) into count FROM tblcustomfeedback WHERE feedbackDate = NOW() ORDER BY Id DESC LIMIT 1;
SET averageScore =avgscore;
SET IdCount = count;
IF (SELECT count(*) FROM tblfeedbackdashbd WHERE Date=CURDATE()) = 0
THEN
INSERT INTO tblfeedbackdashbd(AverageScoresAday ,Date) VALUES(averageScore/IdCount,CURDATE());
ELSE
UPDATE tblfeedbackdashbd SET AverageScoresAday=averageScore/IdCount WHERE Date=CURDATE();
END IF;
END $The error encountered is,
[Err] 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 'COUNT(Id) into count FROM tblcustomfeedback WHERE
feedbackDate = NOW() ORDER BY' at line 11
Solution
The syntax is not:
but it is:
Use:
SELECT id INTO @x, data INTO @y ...but it is:
SELECT id, data INTO @x, @y ...Use:
SELECT SUM(avg_score), COUNT(Id)
INTO averageScore, IdCount
FROM ... ;Code Snippets
SELECT id INTO @x, data INTO @y ...SELECT id, data INTO @x, @y ...SELECT SUM(avg_score), COUNT(Id)
INTO averageScore, IdCount
FROM ... ;Context
StackExchange Database Administrators Q#153872, answer score: 4
Revisions (0)
No revisions yet.