How to find and replace values of one column within the same column (Wordpress wp_postmeta table)

Question

Here is a sample of the table I have:

meta_id | post_id |  meta_key    | meta_value            
1       |  1001   |  Name        |  Jack                   
2       |  1002   |  Name        |  John                   
3       |  1001   |  Description |  Jack is 36 years old   
4       |  1002   |  Description |  John is Jack's brother

Want to find meta_value where meta_key is "Name" in meta_value where meta-key is "Description" and replace it by adding the post_id. E.g. searching for "Jack" in meta_value where meta_key is "Description" and replace it with "Jack (post_id = 1001)".

So the result would be like this:

meta_id | post_id |  meta_key    | meta_value            
1       |  1001   |  Name        | Jack                   
2       |  1002   |  Name        | John                   
3       |  1001   |  Description | Jack (post_id = 1001) is 36 years old   
4       |  1002   |  Description | John (post_id = 1002) is Jack (post_id = 1001)'s brother

Just so you know the table has millions records. Any help would be much appreciated, cheers!

Answer 1

As I said in the comment, you have to loop through every name, to repalce it.

Of course you can't make the run twice, so i would create a new column and put the result there.

Second problem are not unique names for example someone has only the name John, the routine would replace it but lets say there is first a John Malkovitch, that was already replaced, then John would be Replaced twice with different id. Names are also not unique John Claude Smith might exit hundreds of times. An algorithm can't differentiate them.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_name1`()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE _id INT DEFAULT 0;
    DECLARE _descrition TEXT DEFAULT "";
    DECLARE _descritionrep TEXT DEFAULT "";
    DECLARE _post_id INT DEFAULT 0;
    DECLARE _name TEXT DEFAULT ""; 
    -- declare cursor for DESCRITIOn
    DEClARE curnameexch 
        CURSOR FOR 
            SELECT `post_id`, `meta_value` FROM table2 WHERE `meta_key` = 'Name';

    DEClARE curdescritionexch 
        CURSOR FOR 
            SELECT `meta_id`,`meta_value` FROM table2 WHERE `meta_key` = 'Description';


    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

    OPEN curdescritionexch;

    getdesiption: LOOP
        FETCH curdescritionexch INTO _id,_descrition;
        IF finished = 1 THEN 
            LEAVE getdesiption;
        END IF;
        -- build email list
        SET _descritionrep = _descrition;
            OPEN curnameexch;

            getname: LOOP
                FETCH curnameexch INTO _post_id,_name;
                IF finished = 1 THEN 
                    SET finished = 0;
                    LEAVE getname;
                END IF;
                -- build email list
                SET _descritionrep = REPLACE(_descritionrep,_name,
                      CONCAT(_name, ' (post_id = ',_post_id,')'));

            END LOOP getname;
            CLOSE curnameexch;
      --  aLL NAMES EXCHANGED
      UPDATE table2 set `meta_value` = _descritionrep 
      WHERE `meta_id` = _id; 
    END LOOP getdesiption;
    CLOSE curdescritionexch;
END$$
DELIMITER ;

Answer 2

/*
SELECT REPLACE(
  t1.meta_value, t.meta_value, CONCAT(t.meta_value, ' (post_id = ', t.post_id, ')'))
*/
UPDATE t1
FROM table t
LEFT JOIN table t1 ON t1.meta_key = 'Description' AND t.postId = t1.postId
SET meta_value := REPLACE(
  t1.meta_value, t.meta_value, CONCAT(t.meta_value, ' (post_id = ', t.post_id, ')'))
WHERE t.meta_key = 'Name'
;