How to update a MYSQL column if the value's exist in another table?

Question

I have a two tables in my database, tableA and tableB.

tableA:

id | col1  | col2  | in_b
-----------------------
1  | "abc" | "efg" | false
2  | "foo" | "bar" | false

tableB: 

id | col1  | col2  
------------------
1  | "abc" | "bar" 

I want to update the in_b column so that it is true if either col1 or col2's values matches a row in tableB. For example, both in_b flags would be true, because "abc" is in col1 of tableB and "bar" is in col2 of tableB. col1 and col2 are not unique.

What is the best way to update the in_b flag?

Answer 1

I think you want something along the lines of

UPDATE tableA
SET in_b = TRUE
    WHERE EXISTS ( SELECT 1 FROM tableB
                   WHERE (tableB.col1 = tableA.col1 OR tableB.col2 = tableA.col2)
                 )

Answer 2

Based on your question there is no link between the IDs on both table and try to match both columns from tablea with tableb (col1, col2), just a full search for the values. So your update would be:

update tablea a inner join tableb b on ( (a.col1=b.col1 or a.col1=b.col2) or 
                                       (a.col2=b.col1 or a.col2=b.col2) )
   set a.in_b = true;

See it here on fiddle: http://sqlfiddle.com/#!2/3b847/1

In my example I've added a row to the tablea which doesn't have any matches on tableb