MYSQL Function "Error Code: 1242 subquery returns more than 1 row" but I have no subqueries
Solution 1
Your problem is actually quite simple and you'll DEFINITELY remember it in the future... Change your parameter names... from processor_id, method_id to something like parmProcessor, parmMethod
AS IT Stands, your current parameters are the exact same name as the column names you are querying for, and thus
where `processor_id` = processor_id (same with method)
are BOTH referring to actual column name, and 1=1 all day long and 2=2 the same, so you are getting every record.
By changing them to something slightly different as sampled above you would get
where `processor_id` = parmProcessor and `method_id` = parmMethod
which is an EXPLICITLY DIFFERENT implication in the query.
Solution 2
The Query
SET @id := (SELECT `processor_method_id` FROM `processor_method` WHERE `processor_id` = processor_id AND `method_id` = method_id);
could conceivably return more than one record for processor_method_id which is why it's saying the sub-query returns more than one row. Depending on how you want to select the data, you could use the LIMIT
clause.
so it would become:
SET @id := (SELECT `processor_method_id` FROM `processor_method` WHERE `processor_id` = processor_id AND `method_id` = method_id LIMIT 1);
GrayFullBuster
Updated on July 31, 2022Comments
-
GrayFullBuster almost 2 years
I don't know what is the problem why it gives me error in running the function
Here is my sql:
CREATE FUNCTION `test`.`GetProcessorMethodID` (processor_id INT, method_id INT) RETURNS INTEGER BEGIN DECLARE id INT; SET @id := (SELECT `processor_method_id` FROM `processor_method` WHERE `processor_id` = processor_id AND `method_id` = method_id); RETURN @id; END
But when I use this line of sql
SELECT processor_method_id FROM test.processor_method WHERE processor_id = 1 AND method_id = 2;
It works fine!. It gives but the expected value I want to get. But in my function it does not return my expected values and always gives me error and I don't know what is wrong
-
GrayFullBuster over 11 yearsYEs, I tried that already but it always returns the first row, not the row based from processor_id and method_id T_T