huxfurpaw huxfurpaw - 1 month ago 7
MySQL Question

Updating column in table to equal another column in same table WHERE NOT syntax

I have two columns where I'm trying to iron out some mixed data. The two columns that have mixed data are

industry
and
theme
. There are placeholder columns to hold the sussed out data called
industry_temp
and
theme_temp
.

Since there are many more
theme
's than
industry
's, I'm trying to do the following 2 steps, with only the first one working as I had expected:

1) Set industry-temp table to equal all industry types that were mixed into the theme category

UPDATE my_table SET industry_temp=theme WHERE theme='industry_type_1' OR theme='industry_type_2' OR theme='industry_type_3' OR theme='industry_type_4';


2) Set theme-temp table to equal all theme types when the theme type does not equal an industry type.

UPDATE my_table SET theme_temp=industry WHERE industry <> 'industry_type_1' OR industry <> 'industry_type_2' <> OR industry <>'industry_type_3' OR industry <> 'industry_type_4';


Step 2 is not respecting the
<>
operator and adding ALL values from the industry column

Answer

Use NOT IN, the logic is much simpler to follow:

UPDATE my_table
    SET theme_temp=industry
    WHERE industry NOT IN ('industry_type_1', 'industry_type_2', 'industry_type_3', 'industry_type_4');