MySql 5.5.40 stored procedure @@Identity

Question

i've a problem in a stored procedure when i try ovverriding a variable with the value of @@Identity of the last operation. I'll write an example:

DECLARE out_result BIGINT(20);
SET out_result = -1;

UPDATE tableName SET name = "pippo" WHERE id = 12;
SELECT @@Identity INTO out_result;
-- here it works great and out_result value is 12

IF out_result > 0 THAN
    UPDATE otheTableName SET address = "Route 666" WHERE id = 37;
    SELECT @@Identity INTO out_result;
END IF;

I expect that if the first operation was successfull, the variable out_result is greater than 0 and now the code will do the second update and it's so. After the second update (but it may be also a delete for example) i expect that out_result variable will take the value of the second update (in this example out_result = 37) but it's not so. Here out_result is still equal to 12. Some idea will be appreciated. Thanks.

Answer 1

First of all there is no @@Identity in MySQL. You must have mistaken and probably talking about SQL Server. Coming to your case, if you are still getting out_result = 12 then I believe the corresponding UPDATE statement (as pointed below) didn't processed any row because the condition WHERE id = 37 didn't matched (OR) have returned FALSE. You can check and verify the same using @@ROWCOUNT system global variable.

UPDATE otheTableName SET address = "Route 666" WHERE id = 37;