CODEWITHSUNDEEP
mysql
mike-t
mike-t

Reputation: 13

How to compare and replace rows in mysql table?

I have table 'key' with rows

happy new year
I love NY

I have table 'content' with rows

I want to say you: happy new year, Mike
I saw that banner with I love NY really

I would like to find in table 'content' words from table 'key' and replace it with hrefs. The table 'content' will be like

I want to say you: <a href="happy-new-year">happy new year</a>, Mike
I saw that banner with <a href="I-love-NY">I love NY</a> really

Is it possible to make it using mysql syntax?

Upvotes: 1

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

You can get pretty close with this:

update content c join
       keys k
       on c.col like concat('%', k.col, '%')
    set c.col = replace(c.col, k.col,
                        concat('<a href="', replace(k.col, ' ', '-'), '">',
                               k.col, '</a>')
                       );

The way that update works with multiple matches is that only one of the matches takes effect. So, this will only replace one key value. But, it will do it throughout the entire string. In other words, if the same key appears multiple times, then it will be replaced each time.

Upvotes: 1

Related Questions