How to update every each rows in different values in MySQL?

Question

id INT | food TEXT  | memo TEXT         
   1   | Cucumbers  |   NULL
   2   | Dandelions |   NULL
   3   | Salmons    |   NULL
   3   | Cucumbers  |   NULL
   4   | Tomatoes   |   NULL

Evening.

I have a table called results that splits each food values in several rows like the above.

A column named id refers to an individual animal, and a column food is an object what the animals would eat.

I would like to fill up a column memo by using REPLACE or UPDATE keywords like this:

   3   | Salmons    |   Mostly liked it, only 15% didn't eat.
   3   | Cucumbers  |   Only 10% have eaten, rest of all didn't even try. 

A problem is MySQL always updates the same line like this:

   3   | Salmons    |   Mostly liked it, only 15% didn't eat.
   3   | Cucumbers  |   Mostly liked it, only 15% didn't eat.

This is my progress:

DROP FUNCTION IF EXISTS fx_length;
DROP FUNCTION IF EXISTS fx_splitter;
DROP FUNCTION IF EXISTS fx_split_row;
DROP PROCEDURE IF EXISTS App_forEach_memo;

DELIMITER //
    CREATE FUNCTION fx_length(str TEXT, del VARCHAR(2), pos INT)
        RETURNS INT
        RETURN LENGTH(SUBSTRING_INDEX(str, del, pos - 1)) + 1 //

    CREATE FUNCTION fx_splitter(str TEXT, del VARCHAR(2), pos INT)
        RETURNS TEXT
        RETURN SUBSTRING(SUBSTRING_INDEX(str, del, pos), fx_length(str, del, pos)) //

    CREATE FUNCTION fx_split_row(str TEXT, del VARCHAR(2), pos INT)
        RETURNS TEXT
            BEGIN
                DECLARE output TEXT;
                SET output = REPLACE(fx_splitter(str, del, pos), del, '');
                IF output = '' THEN SET output = NULL; END IF;
                RETURN output;
            END //

    CREATE PROCEDURE App_forEach(IN target INT, strings TEXT)
        BEGIN
            DECLARE i INT DEFAULT 1;
            REPEAT
                UPDATE results 
                    SET id = target, memo = fx_split_row(strings, '| ', i) 
                    WHERE id = target;
                SET i = i + 1;
                UNTIL i = target
            END REPEAT;
        END //
DELIMITER ;
CALL App_forEach(3, "Mostly liked it, only 15% didn't eat.| Only 10% have eaten, rest of all didn't even try.");

I think I need to change this part SET id = target . . . for counting per same id numbers, but I don't know how to do this.

Are there any ways to update the memo values w/o creating an extra temp table?

Any tips, suggestions and answers for resolving this problem would be huge appreciated.

Thanks.

Answer 1

WITH cte AS ( SELECT food, ROW_NUMBER() OVER () rn
              FROM results 
              WHERE @id = id )
UPDATE results, cte
SET results.memo = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@memo, '|', cte.rn), '|', -1))
WHERE results.food = cte.food
  AND results.id = @id
  AND cte.rn <= 1 + LENGTH(@memo) - LENGTH(REPLACE(@memo, '|', ''));

fiddle

You may put this query into a procedure if needed.

If you have ancient MySQL version which does not support CTE and window functions then emulate them in subquery using user-defined variables.