CODEWITHSUNDEEP
mysql
Daelendil
Daelendil

Reputation: 47

Error 1242: Subquery Returns More Than 1 Row - why?

I am working on a procedure which sells my items. When I try to call it, it tells me that error message. I checked which part returns more rows, but I couldn't figure it out. Here is the code for that procedure:

DELIMITER //
CREATE PROCEDURE SellItem(pCharacterName varchar(50), pItemName varchar(50), howManyItems int(11))
BEGIN

DECLARE pchar int(11);
DECLARE pitem int(11);
DECLARE pitemCost int(11);
DECLARE pcharMoney int(11);
DECLARE newMoneyValue int(11);
DECLARE cnt int(11) DEFAULT 1;
DECLARE pcharitid int(11);

SET pchar=(SELECT `getChar`(`pCharacterName`));
SET pitem=(SELECT `getItem`(`pItemName`));
SET pitemCost=(SELECT `price` FROM `item` WHERE `item`.`item_id`=pitem);
SET pcharMoney=(SELECT `money` FROM `character` WHERE `character`.`character_id`=pchar);
SET newMoneyValue=pcharMoney;
WHILE cnt<=`howManyItems` DO
SET newMoneyValue=newMoneyValue+pitemCost;
SET cnt=cnt+1;
END WHILE;
SET cnt=1;

WHILE cnt<=`howManyItems` DO
SET pcharitid=(SELECT MAX(`character_item_id`) FROM `character_item` WHERE `character_item`.`character_id`=pchar AND `character_item`.`item_id`=pitem);
IF (SELECT `item_id` FROM `character_item` WHERE `character_item`.`item_id` IN(SELECT `item_id` FROM `item` WHERE `item`.`name`=`pItemName`)) THEN
DELETE FROM `character_item` WHERE `character_item`.`character_item_id`=pcharitid;
UPDATE `character` SET `money`=newMoneyValue WHERE `character`.`character_id`=pchar;
SET cnt=cnt+1;
END IF;
END WHILE;
END; //
DELIMITER ;

The functions I call are simple functions which return me the ID of the selected character/item.

Upvotes: 2

Views: 2238

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

When you call a SELECT in a context that requires only one value, but the SELECT isn't logically guaranteed to return only one value, then you get this error.

You have six SELECT operations:

SET pchar=(SELECT `getChar`(`pCharacterName`));
SET pitem=(SELECT `getItem`(`pItemName`));

These two are calling a stored function on a scalar parameter. They are okay because in both cases, it can only return one value.

SET pitemCost=(SELECT `price` FROM `item` WHERE `item`.`item_id`=pitem);

This SELECT is probably okay. I'm assuming item.item_id is the primary key of that table, so it can match only one row (or zero rows).

SET pcharMoney=(SELECT `money` FROM `character` WHERE `character`.`character_id`=pchar);

Likewise, this seems okay, assuming character.character_id is a primary key.

SET pcharitid=(SELECT MAX(`character_item_id`) FROM `character_item` WHERE `character_item`.`character_id`=pchar AND `character_item`.`item_id`=pitem);

This is sure to return only one value, because you're using MAX() and the query has no GROUP BY.

IF (SELECT `item_id` FROM `character_item` WHERE `character_item`.`item_id` IN
    (SELECT `item_id` FROM `item` WHERE `item`.`name`=`pItemName`)) THEN

The inner subquery doesn't need to be a scalar, because it's part of an IN() predicate, which can compare against multiple values.

The outer query must be a scalar query, but it's not. It is possible to match multiple rows, depending on your data, so it cannot be logically used in an IF() condition.

You can fix it either my using SELECT MAX(item_id)... or by using ...LIMIT 1 at the end of the query.

Upvotes: 2

Blingers
Blingers

Reputation: 883

You are going to have to run each function individually with the test data you are using to establish which one is returning more than one row.

E.g. first run this:

SELECT 'getChar' ('pCharacterName')

But obviously replacing pCharacterName with your test data.

Upvotes: 1

Related Questions