CODEWITHSUNDEEP
mysqlsql
Eddie Padin
Eddie Padin

Reputation: 695

mysql WHERE CLAUSE to exclude rows based on two different values in same column

I'm using MySQL Data Compare to compare a local & remote mysql db.
I'm trying to setup a simple WHERE clause that excludes any rows from the comparison that contain the following values:

%mm or %transient%

The WHERE clause i'm using doesn't seem to be working.

'option_name' NOT LIKE '%_transient_%' OR '%mm%'

The full query that's running is: 

SELECT 'option_id', 'option_name', 'option_value', 'autoload' 
FROM 'wp_options WHERE 'option_name' NOT LIKE '%_transient_%' OR '%mm%'

And the resulting output does not exclude the rows that that have mm or _transient_ in the option_name column.

The caveat here is that I'm limited to only using a WHERE clause & I'm not able to edit the SELECT clause leading up to it (as that's all generated by the software).

Upvotes: 1

Views: 1962

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562911

There is no form of LIKE for comparing to multiple patterns.

As @vkp mentioned in a comment (I don't know why they don't post an answer), your condition won't do what you intend:

WHERE option_name NOT LIKE '%_transient_%' OR '%mm%'

It's not a syntax error in MySQL, because OR can use any expression as operands. Zero values count as false, nonzero values count as true. So your condition is equivalent to:

WHERE (option_name NOT LIKE '%_transient_%') OR ('%mm%')

Which is logically equivalent to:

WHERE (option_name NOT LIKE '%_transient_%') OR (true)

Which will be true on every row, because a true value OR'd together with any other expression will result in true.

Your condition should be this:

WHERE option_name NOT LIKE '%_transient_%' AND option_name NOT LIKE '%mm%'

Upvotes: 2

Related Questions