Find and Replace using Update Statement via Inner Join in MySQL

Question

I'm having two tables namely ds_message and ds_params, the first table contains the template and the second tables contains the key value pair

Table Structure: ds_message

_____________________________________________
id  template                                  
_____________________________________________
1  'Dear {a}, the price of {b} is {c}'       
2  'Dear {i}, you selected the product {j}'

Schema:

CREATE TABLE `ds_message` (
  `id` int NOT NULL,
  `template` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

ALTER TABLE `ds_message`
  ADD PRIMARY KEY (`id`);

INSERT INTO `ds_message` (`id`, `template`) VALUES
(1, 'Dear {a}, the price of {b} is {c}');

INSERT INTO `ds_message` (`id`, `template`) VALUES
(2, 'Dear {i}, you selected the product {j}');

Table Structure: ds_params

_________________________________________________
id   message_id    json_key    json_value
_________________________________________________
1    1             a         John
2    1             b         bat
3    1             c         $10
4    2             i         Emma
5    2             j         Jam

Schema:

CREATE TABLE `ds_params` (
  `id` int NOT NULL,
  `message_id` int NOT NULL,
  `json_key` varchar(500) NOT NULL,
  `json_value` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

ALTER TABLE `ds_params`
  ADD PRIMARY KEY (`id`);

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(1, 1, 'a', 'John');

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(2, 1, 'b', 'bat');

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(3, 1, 'c', '$10');

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(4, 2, 'i', 'Emma');

INSERT INTO `ds_params` (`id`, `message_id`, `json_key`, `json_value`) VALUES
(5, 2, 'j', 'Jam');

I need to replace the keys (for example {a} => John) in the ds_message table.

I tried the following code,

UPDATE ds_message AS t
INNER JOIN ds_params m ON m.message_id = t.id
    SET t.template = REPLACE(t.template, CONCAT('{', m.json_key , '}'), m.json_value);

Once I executed the code I'm getting the output like this, only the first key gets replaced, remaining keys not get update.

_____________________________________________
id  template                                  
_____________________________________________
1   Dear John, the price of {b} is {c}
2   Dear Emma, you selected the product {j}

Kindly assist me how to do this.

@metal I'm using MySQL 8, it need to compatible with 5.7 too — B.Balamanigandan, Aug 22 '19 at 05:49
is there any limit for this, like for one id max number of parameters. — DarkRob, Aug 22 '19 at 06:25
@DarkRob - no limit, it will be dynamic N number of key value pair will be there for a single message. — B.Balamanigandan, Aug 22 '19 at 06:29
@Strawberry - Its an typo error, I corrected the mistake. Thanks. — B.Balamanigandan, Aug 22 '19 at 07:31

score 1 · Answer 1 · answered Aug 22 '19 at 06:30

We have 2 options.

First is using while loop.

SET @n = 0;
SELECT COUNT(*) FROM ds_params INTO @n;
SET @i=0;
WHILE @i<@n DO 
    UPDATE ds_message AS t
    INNER JOIN ds_params m ON m.message_id = t.id AND m.id = @i 
    SET t.template = REPLACE(t.template, CONCAT('{', m.json_key , '}'), m.json_value)
    SET @i = @i + 1;
END WHILE;

2nd Option is this. If you have a fix json_key

UPDATE ds_message AS t
LEFT JOIN ds_params a ON a.message_id = t.id  and a.json_key='a'
LEFT JOIN ds_params b ON b.message_id = t.id  and b.json_key='b'
LEFT JOIN ds_params c ON c.message_id = t.id  and c.json_key='c'
LEFT JOIN ds_params i ON i.message_id = t.id  and i.json_key='i'
LEFT JOIN ds_params j ON j.message_id = t.id  and j.json_key='j'
SET t.template = REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(t.template, CONCAT('{', IFNULL(a.json_key, '') , '}')
                         , IFNULL(a.json_value, '')),
              CONCAT('{', IFNULL(b.json_key, '') , '}'), IFNULL(b.json_value, '')),
              CONCAT('{', IFNULL(c.json_key, '') , '}'), IFNULL(c.json_value, '')),
              CONCAT('{', IFNULL(i.json_key, '') , '}') ,IFNULL(i.json_value, '')),
              CONCAT('{', IFNULL(j.json_key, '') , '}') ,IFNULL(j.json_value, ''));

DarkRob · Answer 2 · 2019-08-22T07:28:06.680

You need to perform loop in this update. Since there is no fix key value pair so it is better to find the max key value pair to perform loop.

After that loop through each record using CURSOR to perform the update to replace your key with corresponding value. Since you already find out the max value of key pair so it will perform only till the max limit of loop for your query. For reference you may find this link for details on CURSOR.


DECLARE  a, b VARCHAR(10);
  DECLARE cur1 CURSOR FOR SELECT DISTINCT json_key,json_value 
  FROM ds_params.t1;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    IF done THEN
      LEAVE read_loop;
    END IF;

UPDATE ds_message AS t
INNER JOIN ds_params m ON m.message_id = t.id
    SET t.template = REPLACE(t.template, CONCAT('{', m.json_key , '}'), m.json_value)
WHERE M.JSON_KEY = b  ;

  END LOOP;

  CLOSE cur1;

I am not sure about syntax but you may get the idea of implementing the same. If anything in syntax is not correct then kindly update the same.

Find and Replace using Update Statement via Inner Join in MySQL

2 Answers2