CODEWITHSUNDEEP
mysqlsql
Ade
Ade

Reputation: 35

Replace substring with result from subsquery

I have a messages table with fields (id, text). I have a CSV file (boilerplate strings) which is loaded into another temporary table tmp_import with field (txt).

Both messages.text and tmp_import are strings. messages can contain boilerplate while tmp_import are all boilerplate strings which I intend to search for occurrences of.

A use case is:

I've loaded the CSV into a temporary table but stuck on how to achieve the replace with a subquery.

    UPDATE messages
    SET text = REPLACE (text, 'string_to_replace', '')
    WHERE text IN  (SELECT txt 
             FROM tmp_import tmp
             WHERE messages.text LIKE CONCAT('%', tmp.txt, '%'));

Any clue on how I can get the string_to_replace to take its value from the txt in the subquery.

Upvotes: 1

Views: 53

Answers (2)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

UPDATE messages
SET text = REPLACE (text, tmp.txt, '')
FROM messages
INNER JOIN tmp_import tmp ON messages.text LIKE CONCAT('%', tmp.txt, '%')

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can use where exists if sub query satisfies the where clause 

UPDATE messages
SET text = REPLACE (text, 'string_to_replace', '')
WHERE EXISTS (SELECT 1
              FROM tmp_import tmp
              WHERE messages.text LIKE CONCAT('%', tmp.txt, '%'));

or use join to get value from tmp_import 

UPDATE messages m
JOIN tmp_import tmp ON m.text LIKE CONCAT('%', tmp.txt, '%')
SET m.text = REPLACE (m.text, tmp.txt, '')

Upvotes: 2

Related Questions