Multiple update query in single prepare execute statement mysql

Question

I google it but I could not find what I need exactly.

I supposed to execute multiple update query in a single statement using PREPARE, EXECUTE in MySQL.

Sample query:

update tableName set column2='a', column3='b' where column1=1; 
update tableName set column2='c', column3='d' where column1=2; 
update tableName set column2='f', column3='g' where column1=3;

SET @Query=myUpdateQuery;
PREPARE stmt FROM @Query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I try using above query but I encountered with an error

Error Code: 1064 You have an error in your SQL syntax;

I am struggling to overcome this error.

Please let me me know the way to accomplish my need. Thanks in advance.

score 1 · Answer 1 · answered Nov 29 '17 at 06:24

This is not possible because PREPARE / EXECUTE stmt can only handle one statement at a time. Confer with the following reference MySQL question:

Execute multiple semi-colon separated query using mysql Prepared Statement

However, you can rephrase your update query into a single statement:

UPDATE tableName
SET column2 = CASE WHEN column1 = 1 THEN 'a'
                   WHEN column1 = 2 THEN 'c'
                   WHEN column1 = 3 THEN 'f' END,
    column3 = CASE WHEN column1 = 1 THEN 'b'
                   WHEN column1 = 2 THEN 'd'
                   WHEN column1 = 3 THEN 'g' END
WHERE column1 IN (1, 2, 3);

score 0 · Answer 2 · edited Jan 14 '18 at 18:08

0

Execute the below sample. Hope it helps

DELIMITER $$
CREATE PROCEDURE proc_name()
BEGIN
    update tableName set column2='a', column3='b' where column1=1; 
    update tableName set column2='c', column3='d' where column1=2; 
    update tableName set column2='f', column3='g' where column1=3;
END$$
DELIMITER ;

edited Jan 14 '18 at 18:08

marc_s

732,580
175
1,330
1,459

answered Nov 29 '17 at 06:23

Thank you very much for your answer. In case, i need to update 1000 row i am not ready to write 1000 update statement. – Vignesh RS Nov 29 '17 at 06:41
1

I **fixed** your typos (it's a **column** - not a "coulum") - so please don't undo my fixes again to reintroduce those typos..... – marc_s Jan 14 '18 at 18:09

score 0 · Accepted Answer · edited Sep 10 '22 at 22:19

Try this. you have to pass comma separated list of the ids you have to update as the first argument of your procedure. Hope this helps.

DELIMITER $$

CREATE PROCEDURE proc_name(IN strIDs VARCHAR(255))
BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
    SET strIDs = '';
  END IF;

do_this:
  LOOP
    SET strLen = LENGTH(strIDs);

    update tableName
    set column2 = 'a', column3 = 'b' 
    where column1 = SUBSTRING_INDEX(strIDs, ',', 1);

    SET SubStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
    SET strIDs = MID(strIDs, SubStrLen, strLen);

    IF strIDs = NULL THEN
      LEAVE do_this;
    END IF;
  END LOOP do_this;

END
$$

DELIMITER ;

If you found any of these suggestions helped you, please dont forget to give an up mark — , Nov 29 '17 at 08:29

Multiple update query in single prepare execute statement mysql

3 Answers3