更新第二个表中主表内的类似关键字

Question

I do have two mysql tables where I would like to mark keywords inside the second table that are inside the main table.

e.g.:

main:

labrador
chihuahua
buldog

second

KEYWORD          SIMILAR
labrador puppies 1
red dogs         0
good medicine    1

Is this even possible with one mysql statement? I would try this with PHP, but hope to find a mysql only solution.

Something like:

UPDATE `keywords` 
SET SIMILAR = 1 
WHERE keyword  like (% SELECT KEYWORD FROM second %")

Has anybody an idea if this is doable and could give me a hint? Thank you for any help on this.

Answer 1

You can connect two tables with joins: MySQL Inner Join Tutorial

UPDATE
    keywords
INNER JOIN 
    second 
    ON keywords.KEYWORD LIKE '%', second.KEYWORDS ,'%'
SET
    keywords.SIMILAR = 1
WHERE
    keywords.SIMILAR = 0;

this query make only an update if the keyword in keywords table similar like keyword in your second table and doesn't yet marked as similar.