How to execute the Update statement in the Stored Procedure in MYSQL-CodePudding

I have Stored Procedure in MYSQL 5.7.33 where I have a select statement in that one, I'm printing update statement and trying to execute that statement to update the values in the table.

My Sample Procedure looks like Below :

  DELIMITER &&  
    ALTER PROCEDURE update_stmt ()  
    BEGIN  
        
   SELECT concat(update Test.sample SET COL = 0 where ID = \'',ID,'\','; ) as stmt FROM 
      Test.Sample into @s; 
       SET @sql = @s
       PREPARE stmt from @sql;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
       
    END &&  
    DELIMITER ;  

  Error  : I'm getting  more than a row.

In the Select Statement I will get multiple update statements like this

Update Test.sample SET COL = 0 WHERE ID = 1 ;
Update Test.sample SET COL = 0 WHERE ID = 2 ;
Update Test.sample SET COL = 0 WHERE ID = 3 ;
...
...
...
Update Test.sample SET COL = 0 WHERE ID = n;

How to execute multiple update statements in Procedure.

Note : This one is small part of the entire procedure which I'm looking for suggestion, based on that I need to add more logic. Thanks

CodePudding user response：

Correct code should be (I assume that Test.sample.id is numeric)

DROP PROCEDURE IF EXISTS update_stmt;

DELIMITER ;;

CREATE PROCEDURE update_stmt ()  
BEGIN  
SELECT concat('update Test.sample SET COL = 0 where id IN (',
              GROUP_CONCAT(id),
              ');')
FROM Test.Sample 
INTO @sql; 
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;;
DELIMITER ;

From the other side your code updates all rows with non-null value in id, so your code performs the same like simple

UPDATE Test.sample 
SET COL = 0 
WHERE id IS NOT NULL;