I am using mysql 5.5.11, when i execute the script below
INSERT INTO payments(created, Amount, user, Remarks, orderid, paymethod)
VALUES('2016-09-03', 0.0, 'admin', '', 4, 'Cash');
I get error
SQL Error: FUNCTION mydb.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
This is the table schema
CREATE TABLE payments (
ID int AUTO_INCREMENT NOT NULL,
OrderID int,
Amount decimal(11,2),
Created varchar(20),
Remarks varchar(160),
user varchar(60),
PayMethod varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci,
/* Keys */
PRIMARY KEY (ID)
) ENGINE = InnoDB;
What could be the cause of the error
This is the trigger attached to the table
BEGIN
/* Trigger text */
UPDATE Orders Set Paid =(Select SUM (Amount) From Payments AS p Where p.OrderID = Orders.ID),PayMethod =new.PayMethod WHere Orders.id = new.OrderID;
UPDATE Orders Set Bal = Total - Paid WHere Orders.id = new.OrderID;
END
Look at trigger:
UPDATE Orders Set Paid =(Select SUM (Amount) From Payments AS p Where p.OrderID = Orders.ID),PayMethod =new.PayMethod WHere Orders.id = new.OrderID;
MySQL does not accept spaces between function name and parenthesis (unless you have set SQL_MODE=IGNORE_SPACE but that gives you other undesirable side effects)
You can check the SQL_MODE setting by running:
SHOW GLOBAL VARIABLES LIKE 'SQL_MODE';
MySQL documentation about this: link
Some example:
mysql> select sum(5);
+--------+
| sum(5) |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)
mysql> select sum (5);
ERROR 1305 (42000): FUNCTION sum does not exist
Solution: Remove space
UPDATE Orders Set Paid =(Select SUM(Amount) From Payments AS p Where p.OrderID = Orders.ID),PayMethod =new.PayMethod WHere Orders.id = new.OrderID;
Remove spaces between SUM
and (
like this: Select SUM(Amount) From Payments
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With