How do I update mutiple rows of a table using Cursor loop?

Question

I have 3 database located in different schema, lets call it

• source_schema.target_table

• target_A.target_table

• target_B.target_table

and wanted to move data from table source_schema to target_A, target_B.

Data in source_schema is judged by source_schema.target_table.target_column using a stored function that return the schema target (A or B).

I tried copying only the id and target_col_val into a new table, and run loop through it, and delete the top rows of the new table as the process ended, but I'm worried about performance, so I gave up on this solution since it require lots of SELECT and DELETE.

Right now this is what I have:

DECLARE target_schema VARCHAR(100);
DECLARE target_col_val VARCHAR(100);
DECLARE target_column VARCHAR(100);
DECLARE tempid VARCHAR(100);

DECLARE cur CURSOR FOR SELECT Id, target_column FROM A.target_table INTO tempid, target_col_val;
OPEN cur;

curloop: LOOP
DO

-- THIS PART OF THE CURSOR LOOP LOGIC
-- FECTH(?)

SET target_schema = get_target_schema(target_table, target_column, target_col_val)


-- UPSERT
INSERT INTO target_schema.target_table (id, name, etc)
SELECT id, name, etc FROM A
WHERE id = tempid;

END LOOP;

Answer 1

After a day of thinking, I reached to this answer and it works like a charm. Hopefully this can help anyone with same problem

CREATE PROCEDURE procedure()
BEGIN

DECLARE target_schema VARCHAR(64);
DECLARE target_column_value VARCHAR(64);
DECLARE temp_table_id VARCHAR(18);
DECLARE fetch_done BOOLEAN DEFAULT FALSE;

-- Declaring the cursor
DECLARE table_cursor CURSOR FOR SELECT Id, target_column FROM target_schema.target_table;
-- Setting the end 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetch_done = TRUE;

-- prepare a template upsert statement
SET @upsert_statement_template = 
    'INSERT INTO $$distribution_schema$$.target_table 
        (id, name, etc)
    SELECT 
        id, name, etc
    FROM 
        source_schema.target_table AS internaltb 
    WHERE 
        internaltb.id = ?
    ON DUPLICATE KEY UPDATE 
        name = internaltb.name, etc = internaltb.etc;';

OPEN table_cursor;

-- cursor loop
TableCursorLoop: LOOP
    FETCH table_cursor INTO temp_table_id, target_column_value;

    -- ending the loop when all the rows are fetched
    IF fetch_done THEN
        LEAVE TableCursorLoop;
    END IF;

    -- get the target schema
    SET @target_schema = get_target_schema('passing the parameter to funtion');
    SET @temp_table_id = temp_table_id;
    
    -- prepare the upsert statement using the template
    SET @upsert_statement = REPLACE(@upsert_statement_base, "$$distribution_schema$$", @distribution_schema);

    -- execute the upsert statement
    PREPARE stmt FROM @upsert_statement;
    EXECUTE stmt USING @temp_table_id;
    DEALLOCATE PREPARE stmt;

END LOOP;
CLOSE table_cursor;

END