huxfurpaw huxfurpaw - 1 year ago 117
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

. There are placeholder columns to hold the sussed out data called

Since there are many more
's than
'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 Source

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');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download