On 09/07/16 14:13, Hal.sz S.ndor wrote:
2016/07/08 09:15 ... Johan De Meersman:
You will have to repeat all the column names - no
wildcards in where clause fieldnames - but the clause
you're looking for is WHERE field IS NULL. Or IS NOT NULL
if you want those:-)
Well, one could try this:
set @sel=(select 'SELECT ' || GROUP_CONCAT(column_name) ||
' FROM membership.address WHERE ' ||
GROUP_concat(column_name || ' IS NULL' SEPARATOR ' OR ')
as cmd
from information_schema.columns where (table_schema,
table_name, is_nullable) = ('membership', 'address', 'yes'));
but changing "membership" and "address" to the right own
database and table name. If one goes this way, I advise
saving the result in a file to be fetched by 'source', not
a variable, because I suspect that one would want to
adjust it to one s own situation.
(After the aforesaid, I did
prepare w from @sel;
execute w;
and got 1000 rows out of 1216; only 216 rows have no NULL
in any field.)
Maybe Lejeczek really wants to make some of his table s
fields NOT NULL?
Thanks! I'm not a sqler and what I needed was a working
piece of a code so I could try to wrap my head around it.
I'd think of something like this as a real world situation:
(and don't mind anything else, eg, poorly design app logic
or/and DB initial design, etc.)
eg. NULL if exists anywhere in a record would simply mean an
incomplete thus useless data, which after some time should
simply be discarded. I thought it would be "sort" of a
common (certainly not unusual) problem.
shame NULL won't work as in:
> select user_id,completetion_time from depression where
NULL in(email_me, other_diagnosis);
or does it?
many! thanks
Now - would searching, saving/storing then removing NULL vs
ALLbutNULL in a simple DB with one table wich has only
primary key be sa
Remember, NULL is a special value that is not the same as
zero or the empty string; nor to itself: NULL != NULL, by
definition. Personally, I mostly discourage the use of it
(use DEFAULT VALUE in your table definition wherever
possible) except in circumstances where it really is
necessary to know the difference between 'nothing here'
and 'we have no information about this at all'.
In SQL NULL has too many uses. It is as if they got this
bright idea of NULL as you describe and all over used it
(x/0 ?).
We have fields "HomePhone", "CellPhone", and "WorkPhone"
and allow them to be NULL, not because maybe we don't
know, although that sometimes is true, but because in the
expression
CONCAT_WS(', ', 'c' || CellPhone, HomePhone, 'w' ||
WorkPhone)
any NULL argument (after the first) so conveniently
completely disappears from the result.
When one makes a field NOT NULL in MySQL one also uses
slightly less storage.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
