CODEWITHSUNDEEP
mysqltriggers
cccc
cccc

Reputation: 65

MYSQL select into multiple rows

I have this trigger where it checks a table named auctions for auctions that have ended. Once the auction ends, a winner is placed into the column. The trigger then checks whether there is a valid winner. The problem is when its checking the auctions tab and one person has won 2 bids or more. It says resulting row has more than one columns, which makes sense because the person won 2 or more rows. My question is how I can use SELECT INTO to insert multiple rows with the same "winner".

DELIMITER ;

 DELIMITER $$
CREATE TRIGGER notify_winner
    AFTER UPDATE ON Auctions
    FOR EACH ROW 
BEGIN

    SELECT A.winner, A.vin INTO @buyer, @vin
    FROM Auctions AS A WHERE A.winner != 'NONE' AND A.winner IS NOT NULL;

    INSERT INTO Wins (winner,vin)
    values (@buyer, @vin);



END$$
DELIMITER ;

Upvotes: 2

Views: 3038

Answers (1)

Leonardo Gabriel
Leonardo Gabriel

Reputation: 347

Actually you can't. Select Into is used when has just one row as result. Use cursor instead. For example:

DELIMITER $$
CREATE TRIGGER notify_winner
  AFTER UPDATE ON Auctions
  FOR EACH ROW 

BEGIN
  DECLARE done INT DEFAULT FALSE;

  DECLARE cur1 CURSOR FOR 
  SELECT A.winner, A.vin 
    FROM Auctions AS A 
   WHERE A.winner != 'NONE' 
     AND A.winner IS NOT NULL;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO @buyer, @vin;

    IF done THEN
      LEAVE read_loop;
    END IF;

    INSERT 
      INTO Wins (winner,vin)
    values (@buyer, @vin);

  END LOOP;

  CLOSE cur1;

END$$
DELIMITER ;

For more information take a look at this link: https://dev.mysql.com/doc/refman/5.7/en/cursors.html

Hope it helps!

Upvotes: 1

Related Questions