how can i make a mysql batch update stored procedure?

Question

So I wanted to create a stored procedure to update a batch of rows. I usually do this with looping the stored procedure in my code behind but this has some performance setback. My problem is how to pass the parameters to the query since the total number of rows to be changed varies.

This is my single row update stored procedure, how can I convert it to process more than 1 rows and how can I pass those rows as parameters?

DROP PROCEDURE IF EXISTS EditProductType;
DELIMITER $$

CREATE PROCEDURE EditProductType(
  IN productTypeId INT,
  IN NAME VARCHAR(100),
  IN details VARCHAR(1000)
)
BEGIN

UPDATE product_types pt
SET pt.name = NAME,
    pt.details = details
WHERE pt.productTypeId = productTypeId;


END$$

DELIMITER ;

Answer 1

It is not practical to UPDATE more than one row at a time unless

• The WHERE clause references multiple rows (without any variation)

OR

• You have a table with the changes and you use a multi-table UPDATE that JOINs your table and that table.

IODKU

I consider this to be an ugly solution, but I will present it anyway.

INSERT INTO t (id,b,c)
    VALUES
    (10,'doohickie',3),
    (22,'whatchamacallit',44),
    (98,'doodad',55),
    (34,'thingamabob',56)
ON DUPLICATE KEY UPDATE
    b = VALUES(b),
    c = VALUES(c) ;

Assumptions:

• id is the PRIMARY KEY (or a UNIQUE key).
• You have specified rows that already exist; that way, the INSERT half of IODKU won't be used, just the UPDATE half.

If you already have id,b,c in a table temp, this would work (probably better):

INSERT INTO t (id,b,c)
    SELECT id,b,c  FROM temp
ON DUPLICATE KEY UPDATE
    b = VALUES(b),
    c = VALUES(c) ;