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.
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.
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.
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
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
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".
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;
+--------+--------+
| 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.
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='';
UPDATE TEST set testA='b' where testA<>'a';
How is it possible that all NULL values equal any string?
If this question does not belong in this list I will make no additional
postings concerning this topic and will continue researching other sources.
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!).
- Matthew
-----Original Message-----
From: Jochem Maas [mailto:jochem@xxxxxxxxxxxxx]
Sent: Monday, January 03, 2005 2:16 PM
To: php-db@xxxxxxxxxxxxx
Subject: Re: NULL VALUE
Norland, Martin wrote:
>>-----Original Message-----
>>From: Jochem Maas [mailto:jochem@xxxxxxxxxxxxx]
>>Sent: Monday, January 03, 2005 12:16 PM
>>Subject: Re: NULL VALUE
>>
>>LightBulbMoment (tm): 5 seconds of searching on the MYSQL site tells
>
> me
>
>>STATUS is a keyword. try either renaming your field or using backticks
>
>
>>to escape the name e.g.:
>
>
> http://dev.mysql.com/doc/mysql/en/Reserved_words.html doesn't list
> status, although I am familiar with its various forms e.g. "show
> status".
I didn't even go as far looking there.... I made the assumption that
mysql was crapping out on 'STATUS' because of its use in 'SHOW STATUS'
query (and the like) - also its hard to determine whats going wrong with
someones setup when you have no idea what version of stuff they are
running - bare in mind we assume the chap (Matthew Perry) in question is
using MySQL but he did not state this so that may be incorrect.
at any rate dumping the output of mysql_error() or mysqli_error() would
probably have saved him an email to this list. ;-)
>
> You're probably right, but I don't exactly see this as a 'clearly
> documented'. Still, backticking all field names is a good idea anyway.
I didn't mean to say it was clearly documented (IMHO reading &
understanding documentation is one of the hardest parts of programming!)
but it took me exactly 5 seconds to determine with a 95% certainty that
'STATUS' was causing his query to crap out.
HINT TO 'BEGINNERS':
1. give background to your problem. (context, software versions etc)
2. show that you have taken the time to research it.
3. google on how to successfully pose a question on a mailing list -
there are a number of very explicit essays on the subject!
4. don't give up, we have all struggled for hours/days/weeks on the
tiniest of problems.
5. don't assume somebody will do it for you if you can't be bothered!
6. expect the occasional RTFM, even when you're doing everything right
;-)
> Builds character.
:-)
>
> Cheers,
>
> - Martin Norland, Database / Web Developer, International Outreach x3257
> The opinion(s) contained within this email do not necessarily represent
> those of St. Jude Children's Research Hospital.
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
