How to update each entry of a table for each entry of another table in MySQL

I try to update a table by replacing text in it by a mapping table. The solution I came up with works, but only for the one entry.

How can I update all entries of item for each entry in the id_mapping table?

Example tables:

DROP TEMPORARY TABLE IF EXISTS `item`;
DROP TEMPORARY TABLE IF EXISTS `id_mapping` ;


CREATE TEMPORARY TABLE IF NOT EXISTS `item` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `type` INT DEFAULT 1,
    `text` VARCHAR(200) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MEMORY;

CREATE TEMPORARY TABLE IF NOT EXISTS `id_mapping` (
    `old_id` INT NOT NULL,
    `new_id` INT NOT NULL,
     PRIMARY KEY (old_id, new_id)
)
COLLATE='utf8_general_ci'
ENGINE=MEMORY;

INSERT INTO `item` (`type`, `text`) 
VALUES 
    (1, '<span><a href="item_id=111">Link</a></span>'),
    (1, '<span><a href="item_id=222">Link</a></span>'), 
    (1, '<span><a href="item_id=333">Link</a></span>'),
    (2, '<span><a href="item_id=444">Link</a></span>');

INSERT INTO `id_mapping` (`old_id`, `new_id`) 
VALUES 
    (111, 999),
    (222, 888),
    (333, 777),
    (444, 666);

item

id	type	text
1	1	<span><a href="item_id=111">Link</a></span>
2	1	<span><a href="item_id=222">Link</a></span>
3	1	<span><a href="item_id=333">Link</a></span>
4	2	<span><a href="item_id=444">Link</a></span>

id_mapping

old_id	new_id
111	999
222	888
333	777
444	666

The update query I came up with that should update all entries, but actually only updates the first entry:

UPDATE `id_mapping` m, `item` i

SET i.`text` = REPLACE(
    i.`text`, 
    CONCAT('item_id=',m.old_id), 
    CONCAT('item_id=',m.new_id)
)
WHERE i.`type` = 1;

After execution of the update it changed to:

item

id	type	text
1	1	<span><a href="item_id=999">Link</a></span>
2	1	<span><a href="item_id=222">Link</a></span>
3	1	<span><a href="item_id=333">Link</a></span>
4	2	<span><a href="item_id=444">Link</a></span>

What I expected:

item

id	type	text
1	1	<span><a href="item_id=999">Link</a></span>
2	1	<span><a href="item_id=888">Link</a></span>
3	1	<span><a href="item_id=777">Link</a></span>
4	2	<span><a href="item_id=444">Link</a></span>

Am I doing something wrong with the basic concept of this?

Answer

You need to join tables in a way. For example

UPDATE  `item` i
JOIN `id_mapping` m ON i.text like concat('%item_id=', m.old_id,'%' ) 
SET i.`text` = REPLACE(
    i.`text`, 
    CONCAT('item_id=',m.old_id), 
    CONCAT('item_id=',m.new_id)
)
WHERE i.`type` = 1;