How to check if any value is Null in a table single row SQL

Question

Each row of data in my table has about 8 elements. I want to check to see if any of the elements = NULL in a certain row, something like this:

UPDATE item_list SET is_tradable='no' WHERE item_name=:itemname AND element_1 = NULL OR element_2 = NULL or element_3... etc.

Can I do the same thing but check if any of them are NULL without going through each item? Like:

UPDATE item_list SET is_tradable='no' WHERE item_name=:itemname AND anyElement = NULL;

http://stackoverflow.com/questions/18415820/mysql-is-it-possible-to-use-like-on-all-columns-in-a-table — Rakesh Shewale, May 11 '15 at 10:45

score 1 · Accepted Answer · answered May 11 '15 at 10:40

No, you cannot do what you want. You need to list all the columns out.

However, your query will not do this. You need to use IS NULL rather than = NULL:

UPDATE item_list
    SET is_tradable = 'no'
    WHERE item_name = :itemname AND
          (element_1 IS NULL OR element_2 IS NULL or element_3... etc.)

Also, remember the parentheses when mixing AND and OR.

There are ways to "simplify" the calculation. For instance, concat() will return NULL if any argument is NULL:

UPDATE item_list
    SET is_tradable = 'no'
    WHERE item_name = :itemname AND
          concat(element_1, element_2, . . . ) IS NULL

As do the arithmetic operators (but your question does not state the type of the arguments).

score 1 · Answer 2 · answered May 11 '15 at 10:41

There is a way but you need to mention all the column names in null comparison something as

mysql> select least(null,'aa',null,'bb') ;
+----------------------------+
| least(null,'aa',null,'bb') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

Here its getting the least values from a group of values.

So you may use as

WHERE item_name=:itemname 
AND least(element_1,element_2,element_3...) is null

How to check if any value is Null in a table single row SQL

2 Answers2