CODEWITHSUNDEEP
mysql
Hossein Amini
Hossein Amini

Reputation: 737

Get ID of Table and store in Variable

I want to store the last updated row's id to a variable and then set a trigger to update some cells, the code for trigger:

BEGIN
DECLARE MYROW_ID INT;
DECLARE WITHDRAWAL DECIMAL(11,1);
DECLARE USERNAME VARCHAR(10);
SET MYROW_ID = SELECT id FROM withdrawals ORDER BY lastmodified DESC LIMIT 1;
SET WITHDRAWAL = SELECT withdrawal FROM withdrawals WHERE id = MYROW_ID;
SET USERNAME = SELECT username FROM withdrawals WHERE id = MYROW_ID;
UPDATE  `interests` SET  `totalincome` =  `totalincome` - 'WITHDRAWAL'
WHERE 'username' = 'USERNAME' AND 'status' = 1;
END

MySQL gives my error for 5th line, Am I assigning wrong data type i.e. MYROW_ID?
How can I store returned value from SELECT into MYROW_ID?

Upvotes: 0

Views: 2162

Answers (2)

Hossein Amini
Hossein Amini

Reputation: 737

i just put the statement between parentheses:

SET MYROW_ID = (SELECT id FROM withdrawals ORDER BY lastmodified DESC LIMIT 1);

Upvotes: 1

Nikhil Batra
Nikhil Batra

Reputation: 3148

You should do it like this:

SELECT id into MYROW_ID FROM withdrawals ORDER BY lastmodified DESC LIMIT 1;

Upvotes: 1

Related Questions