Hello!

Is there an SQL catchall value? In other words, if the result of the query is null, then put a value into the fields.

Thanks!
Murdock

Actually, NULL is a value and you can test for it; e.g., ... set somecolumn = 0 where blah is null ... Specifically is there a catchall value, well, no unless you want to consider NULL as such (and it pretty much is).

Hello,

Thanks for the pointer.

So technically, the way to change a Null value into something else would be to code:

mysql> set @a = select man from people where people.man = "charlie";
mysql> set @a = "Bob" where @a is Null;

Thanks,
Murdock

You would seldom want a default in a "select" (you probably don't want to retrieve a value other than then one that's actually there!) ...

... but most RDBMS's (including mySQL) support a "default" clause any any column for inserts:

http://dev.mysql.com/doc/refman/5.0/...-defaults.html

You may want to consider altering your tables so that numeric columns default to zero (integer 0, float 0.0) and important character columns (for example, fname and lname) are not null; i.e., a value is required, and non-critical character columns default to NULL. The idea is to not add a bunch of stuff that just takes up space but at the same time let the engine force you to make entries in critical columns. Defaulting a numeric column to zero will only take up storage space the size of the given data type (an integer and a float typically take up four bytes, a double typically eight bytes). This starts to matter when you get a few 10's of million rows in your tables. That a character is null doesn't really matter because you can test for that; i.e., the result of a query is either "something" or "nothing" and you take appropriate action depending upon that.

Hello,

Thanks for the info.

How do take action after a select query?

Also, what kind of syntax do you use to add a default statement to a select statement?

Thanks,
Murdock

If you want to convert table.column values to something else if eg they are null, see this page: http://dev.mysql.com/doc/refman/5.0/...functions.html

You don't add a default in a select statement; you do it when you initially design the table or you use alter table after the fact; see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html for the syntax.

SQL is kind of bassackwards to the way you normally think -- you tend to think in terms of "if condition do this else do that," and SQL isn't quite the same. So, you don't take action after a query in SQL, you take action as part of the query.

Something like this, say

update tabname
set col_a = "this string value"
where col_a is null
and col_b = 123
and col_c = 0;

So, the content of col_a will only be changed to "this string value" if col_a is null and the other two values are as stated. Not quite the same thing as "if - else."

Hope this helps some.