SELECT INTO multiple @variables MySQL

13,856

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.

Share:
13,856
Ríomhaire
Author by

Ríomhaire

Updated on June 17, 2022

Comments

  • Ríomhaire
    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 in album. scene_id is a field in album. id is the PRIMARY_KEY of album.

    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