Inserting multiple rows into a single row mysql

Question

The following script is intended to have the following functions:

1. find a row in eurusd_m1 where volume=250,
2. find the row before row 1,
3. find the row after row 1,

4. copy the required values from each of these rows into a single row in obsh4

   Create procedure doji_result ()
   begin 
   
   DECLARE initial DATETIME;
   DECLARE final DATETIME;
   DECLARE centre DATETIME;
   DECLARE x int;
   
   SET x = 0;
   
   SET @initial = (select MQLTime from eurusd_m1 where volume=250 
   order by mqltime asc limit 1);
   
   SET @final = (select MQLTime from eurusd_m1 where volume=250 
   order by mqltime desc limit 1);
   
   REPEAT
   
   SET @centre = (select MQLTime from eurusd_m1 where volume=250 
   order by mqltime asc limit x,1);
   
   INSERT INTO obsh4 (MQLrefTime,RrefTime,Open,High,Low,Close,Volume) 
   select MQLTime,RTime,Open,high,Low,Close,Volume 
   from eurusd_m1 where MQLTime = @centre 
   order by MQLTime asc limit 1; 
   
   INSERT INTO obsh4 (Open2,High2,Low2,Close2,Volume2) 
   select Open,high,Low,Close,Volume 
   from eurusd_m1 where MQLTime < @centre 
   order by MQLTime desc limit 1; 
   
   INSERT INTO obsh4 (Open3,High3,Low3,Close3,Volume3) 
   select Open,high,Low,Close,Volume 
   from eurusd_m1 where MQLTime > @centre 
   order by MQLTime asc limit 1; 
   
   SET x=x+1;
   
   UNTIL @centre=@final
   END REPEAT;
   
   END $$
   DELIMITER ;
   

   When I run this each row from eurusd_m1 is copied to a new row in obsh4 (rather than all three rows being collapsed into a single row with each loop iteration)

   I tried to use UPDATE for adding the second and third row of data using the following script however I get a syntax issue on FROM and I'm not sure how to remedy that either :/

   update obsh4
   set  Open2 = open,
        High2 = high,
        Low2 = low,
        Close2 = close,
        Volume2 = volume, 
   from select Open,high,Low,Close,Volume from eurusd_m1
   where
   MQLTime < @centre order by MQLTime desc limit 1; 
   

I can see three options:

1) the update statement needs to be remedied

2) there needs to be row specification added to the insertion of the second/third rows

3) binning and generating something from scratch.

Any suggestions on either of these would be most welcome. Thank you

Edit: the loop itself operates correctly and has been tested independently of the current problem.

Answer 1

Problem answered with this lengthy piece of code. I think I could be simplified somewhat so I am well open to suggestions.

drop procedure if exists doji_result; 
DELIMITER $$
Create procedure doji_result ()
begin 

DECLARE final DATETIME;
DECLARE centre DATETIME;
DECLARE openB1 DOUBLE;
DECLARE highB1 DOUBLE;
DECLARE lowB1 DOUBLE;
DECLARE closeB1 DOUBLE;
DECLARE volumeB1 DOUBLE;
DECLARE openA1 DOUBLE;
DECLARE highA1 DOUBLE;
DECLARE lowA1 DOUBLE;
DECLARE closeA1 DOUBLE;
DECLARE volumeA1 DOUBLE;

DECLARE x int;

SET x = 0;

SET @final = (select MQLTime from eurusd_m1 where volume=250 
order by mqltime desc limit 1);

REPEAT

SET @centre = (select MQLTime from eurusd_m1 where volume=250 
order by mqltime asc limit x,1);

SET @openB1 = (select open from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @highB1 = (select high from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @lowB1 = (select low from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @closeB1 = (select close from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @volumeB1 = (select volume from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);

SET @openA1 = (select open from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
SET @highA1 = (select high from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
SET @lowA1 = (select low from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
SET @closeA1 = (select close from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
SET @volumeA1 = (select volume from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);

INSERT INTO obsh4 (MQLrefTime,RrefTime,Open,High,Low,Close,Volume) 
select MQLTime,RTime,Open,high,Low,Close,Volume 
from eurusd_m1 where MQLTime = @centre 
order by MQLTime asc limit 1; 

update obsh4
SET open2 = @openB1,
    high2 = @highB1,
    low2 = @lowB1,
    close2 = @closeB1,
    volume2 = @volumeB1,
    open3 = @openA1,
    high3 = @highA1,
    low3 = @lowA1,
    close3 = @closeA1,
    volume3 = @volumeA1
order by mqlreftime desc limit 1;

SET x=x+1;

UNTIL @centre=@final
END REPEAT;

END $$
DELIMITER ;