Hi Matthew,
there are some more comments below but just for the record could you confirm that you are retrieving one or more rows when you issue the following SQL:
SELECT * FROM `employee` WHERE `status` IS NULL;
if you are getting no rows back then the obviously updating based on the status field being NULL will update nothing.
Perry, Matthew (Fire Marshal's Office) wrote:
I apologize for my lack of information concerning this problem and I appreciate the "advice for beginners". I also did not mean to post it on this list (If you notice my original message also contained a thank you for a question never asked in this list). I am terribly sorry about this error.
well don't be too sorry :-) ... spilt milk and all that!
HOWEVER: The question can regardless be addressed in this list since it is not appear to be version or database specific. I am using MySQL 4.1 but have experienced the same problem with MS SQL Server.
ok. Don't have any experience with MSSQL. (and I'm more of a firebird/interbase kinda guy these days)
The issue here is NOT whether STATUS is a keyword. Yes STATUS is a keyword and indeed it would probably be wise if I did not use keywords as column names. The reason I chose to leave this column named "STATUS" is that it is linked to other databases (ouch!) and such attempts have proven to cause even more problems.
ouch indeed.
But in theory this does not matter. One can use keywords as column names in all recent versions of MySQL even though this is unadvised (you have to search the MySQL site for longer than 5 seconds to learn this). The problem
;-) you are correct in this, although I would never ever use keywords as column names without quoting (i.e. backticks in MySQL) those names in all queries (even if it does work without quotes on certains versions/dbs/platforms/etc)
exists for all column names. I have experimented with other test tables with more standard column names and have had the same result.
mysql_error() or mysqli_error() also do not help here. Even if you enter the SQL command directly it posts a result that changes 0 rows and the query completes without any errors.
I believe this problem lies in how SQL handles NULL values. Let me change
lets be clear here: SQL in a generic term, there are various ANSI standards that define the SQL specifications BUT no one implementation full supports any standard - the handling of the NULL value is DB dependent (that is to say there will be probably inconsistencies)
[the great thing about standard is there are so many to choose from ;-)]
the column name to avoid the question concerning STATUS as a keyword.
UPDATE EMPLOYEE SET STATUS_lsdkrjg='Inactive' where STATUS != 'Active'; UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS <> 'Active'; UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS IS NULL'; UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS = ''; UPDATE EMPLOYEE SET STATUS_lsdkrjg ='Inactive' where STATUS = NULL;
None of these work to update table "Employee".
that does not surprise me, only half of the 'STATUS' fieldnames in those queries have had their names changed....
According to some of my SQL reference manuals they should!
I have tried the following and had success with one of these options:
create table TEST ( testA varchar(30), testB varchar(30) ); insert into TEST values ('a', 'a'); insert into TEST values (NULL, 'a');
+--------+--------+ | testA | testB | +--------+--------+ | a | a | | [NULL] | a | +--------+--------+
UPDATE TEST set testA='b' where testA=''; UPDATE TEST set testA='b' where testA<>'a';
+--------+--------+ | testA | testB | +--------+--------+ | a | a | | [NULL] | a | +--------+--------+
Finally I tried this: UPDATE TEST set testA='b' where testA IS NULL;
this last statement is the proper way of doing it.
+--------+--------+ | testA | testB | +--------+--------+ | a | a | | b | a | +--------+--------+
But the last example does not work for ANY fields in my EMPLOYEES table. I thought at first the problem might have arisen with the fact that this table is linked to other databases, but even after I removed the links this problem exists!
I think the problem still lies with my understanding of NULL.
you should realise that you can never do something like:
SELECT * FROM mytable WHERE myfield=NULL
the reason being that the NULL value is akin to 'unknown' and having an unknown value be equal to another unknown value is rather illogical (at least that how the gurus of old decided we should live - if you catch my drift)
in short NULL is not equal to anything at all, ever - NULL is not even equal to NULL.
What am I forgetting about the nature of NULL values? Why won't the following work for NULL values? UPDATE TEST set testA='b' where testA='';
NULL is not equal to an empty string. see above. also check out the searchable online version of the manual at mysql.com where they (try to) explain this in alot more detail (with loads of SQL statements to try which make the whole concept alot more tangible)
UPDATE TEST set testA='b' where testA<>'a';
I would have thought this would work - but apparently it doesn't (I did some checking)
How is it possible that all NULL values equal any string?
as far as I know its not possible.
If this question does not belong in this list I will make no additional postings concerning this topic and will continue researching other sources.
seems like a pretty decent place to ask DB related questions, I think we can assume you are using PHP otherwise you probably would never have hit this mailing list.
Once again I apologize for the original posting. It was meant to go to one of my associates (who also does not know the answer!).
humbug!
at least you can be sure that you are going to understand the NULL concept a whole lot better once you have figured this out...
rgds, Jochem
- Matthew
....
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
