SELECT INTO multiple @variables MySQL
You can join with the same table and ensure that each join will provide a new id, something like (eg. for two ids, but you will get the point):
SELECT a1.id, a2.id INTO @photo1, @photo2
FROM album a1
inner join album a2 on a2.scene=a1.scene and a2.upload=a1.upload and a2.id>a1.id
WHERE a1.uploaded = @time AND a1.scene_id = NEW.id;
See SqlFiddle for a complete sql and test case.
Ríomhaire
Updated on June 17, 2022Comments
-
Ríomhaire over 1 year
I wish to store the result of a
SELECT
statment into multiple variables.I know that the result of this query will always return
6 ints
from 6 different rows.I have tried using the following code :
SELECT id INTO @photo1, @photo2, @photo3, @photo4, @photo5, @photo6 FROM album WHERE uploaded = @time AND scene_id = NEW.id;
album
is a table.uploaded
is a field inalbum
.scene_id
is a field inalbum
.id
is thePRIMARY_KEY
ofalbum
.I have read that the number of variables must equal the number of fields. Which is obviously not the case in the above statement.
With this in mind, how would I overcome this problem?
This code is being used within a MySQL Trigger.
EDIT : Relevant Table schema as per request :
person -- name of table id | approved -- id is PK album -- name of table id | uploaded | scene_id -- id is PK
Trigger is fired on change of
approved
from 0 to 1