MySQL not and not equal operators fail, what gives?

Question

I have a table T that has a column A. A started with a default of NULL. 40 rows later, I changed the default to 1. Three rows had a value of 2. I tried to select all the rows where column A where not 2 and set them to the new default of 1 (which hadn't happened automatically when I altered the table). I first tried:

update T set A=1 where A != 2;

Nada. Didn't work. Selected zero rows. Next I tried:

update T set A=1 where !(A=2);

Nope, nothing there either. I tried plugging them into selects, to see if there was something wrong with the update, but those returned nothing either. The MySQL reference manual says that != and ! are valid operators and ought to be perfectly valid in that context. I finally achieved my goal using IS NULL, but those statements should have worked. So what gives? Why didn't that work?

I am running MySQL version: 5.1.41-3ubuntu12.6 (Ubuntu)

D'Arcy Rittich · Accepted Answer · 2010-09-04 10:53:16Z

3

Try:

update T set A=1 where A != 2 or A is null;

Comparisons involving NULL evaluate to NULL(UNKNOWN) and will thus never be true.

answered Sep 4, 2010 at 10:53

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Bingham Over a year ago

Mentioned in the question that I already achieved my goal using IS NULL. Meaning, I already did that. But thanks for the bit about comparisons involving NULL!

Collectives™ on Stack Overflow

MySQL not and not equal operators fail, what gives?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related