MySQL SELECT INTO statement: Beware of 0, 2 or more result rows!
Hi all,
I've been struggling for an hour to try and figure out how to retrieve a bit type value from one table and insert it into another table in a stored procedure. I've tried casting, changing variable types, etc. Nothing works. Here's a simplified example of what I'd like to do: Code:
(stored procedure introductory code omitted, no problem there) What's strange is that this works (in the script)!: Code:
UPDATE table2 SET pos_info_t2 = 9 WHERE id = _id; Also, if I go directly into the mysql terminal and type the same two lines and declaring a variable @a to use for the transfer, it works! But the equivalent never works in the script, in the procedure! I've tried pos_info+0 (as INT and as BIT(4). I've tried BIN(pos_info+0) (as BIT(4)). I've tried many other possibilities, it never works! Does anybody have the solution to the problem? Thanks in advance! rm |
Okay, okay... I don't think the problem is the bit data type, I think it has something to do with the varchar variable.
Here's the entire script: Code:
DELIMITER | What's strange is if I uncomment the IF statement, it works for the word in question: Code:
IF _name = "erreur" THEN I just can't figure it out! Is "erreur" assigned to _name in the IF statement? Is that why it works with the IF statement and not otherwise? Obviously, without the IF statement, the idea is to take values from one table and insert them in another. Now, not all of the queries in the SELECT statement will yield results (I'm talking about the inner SELECT statement, not the CURSOR one). Some will have no rows as a result. Might this have something to do with it? If so, how do I change my code so that it works? Many thanks, rm |
Okay, I've finally figured it out!
It was neither a bit data type problem nor a strange "IF assignment", the problem is that some of the queries in the inner SELECT statement yielded no results, in which case (I'm not sure about this, exactly), the script stopped executing completely, exiting the cursor. This made it work: Code:
SET _count = 0; The MySQL Reference web pages says this about SELECT INTO... (variables): Quote:
Perhaps someone out there knows more about this condition and how to (better) deal with it? If so, you're comments would certainly be appreciated. |
Well, database questions aren't all that sexy... No response... I'm therefore going to mark this thread as SOLVED!!
rm |
All times are GMT -5. The time now is 02:08 PM. |