Which is faster? Multiple update queries in a cursor loop, or a single query?

Which is faster? Multiple update queries in a cursor loop, or a single query?
For example, in this case, which is faster? This query is in a trigger.

-
Multiple Query in loop

DECLARE done INT DEFAULT FALSE;
DECLARE record_id INT;
DECLARE cur CURSOR FOR SELECT id FROM table1 WHERE column = 0;

OPEN cur;

  users_loop: LOOP

     FETCH cur INTO id;

     IF done THEN
         LEAVE users_loop;
     END IF;

     UPDATE table2 SET column = 0 WHERE id = id;

  END LOOP;

CLOSE cur;

-
Single Query

CREATE TEMPORARY TABLE tmp_table (id int(10));

 INSERT INTO tmp_table SELECT id FROM table1 WHERE column = 0;

 UPDATE table2 SET column = 0 WHERE id IN(SELECT id FROM temp_table);

 DROP TABLE IF EXISTS tmp_table;

Neither -- You need to present a 3rd option.

Option 1 is slow because it is going back and forth between the Stored Routine and the server. (This is not as bad as between the Client and Server, but it is still extra effort.)

Option 2 is slow because of the tmp table and subquery.

Option 3 is something like

UPDATE   table2
    JOIN table1  ON table1.id = table2.id
    SET   table2.column = 0
    WHERE table1.column = 0;

(I assume id is the PRIMARY KEY in each table?)

Important: table1 needs INDEX(column).

See "multi-table update" in the online reference manual.

StackExchange Database Administrators Q#153869, answer score: 4