On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
Ok, I have figured out that I do not have null values on the data of my
table, even though I do have empty strings. Here is my schema,
sqlite>
sqlite> .schema
CREATE TABLE LSOpenJobs
(
id integer primary key, ProjID integer, parent, children, login,
cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor,
vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid,
notes, status
);
And here is a call for a SELECT for a null value:
sqlite> select * from LSOpenJobs where notes IS NULL;
sqlite>
Now, I know for a fact that there are notes empty on some of the records
there. Why is sqlite thinking that I do not have null values?
you know for a fact that some of the notes are empty, but what makes
you think they are null? Did you create them as null or did you get
this db from someone?
Here is a simple test --
Lucknow:~ punkish$ sqlite3
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> CREATE TABLE foo (a, b);
sqlite> .s
CREATE TABLE foo (a, b);
sqlite> INSERT INTO foo (a) VALUES ('blah');
sqlite> INSERT INTO foo (b) VALUES ('blech');
sqlite> .headers on
sqlite> .mode columns
sqlite> SELECT * FROM foo;
a b
---------- ----------
blah
blech
sqlite> SELECT * FROM foo WHERE a IS NULL;
a b
---------- ----------
blech
sqlite> SELECT * FROM foo WHERE b IS NULL;
a b
---------- ----------
blah
sqlite> UPDATE foo SET b = '' WHERE a = 'blah';
sqlite> UPDATE foo SET a = '' WHERE b = 'blech';
sqlite> SELECT * FROM foo;
a b
---------- ----------
blah
blech
sqlite> SELECT * FROM foo WHERE a IS NULL;
sqlite> SELECT * FROM foo WHERE b IS NULL;
sqlite>
----- Original Message -----
From: "jose isaias cabrera" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Saturday, March 10, 2007 11:40 PM
Subject: [sqlite] UPDATE colomns based on their values
>
> Greetings!
>
> So, I would like to update some columns based on whether or not they are
> null. Some of you folks have helped me with ifnull and IS NULL, and I
> happened to see coalesce, but none of these are working. If I set the
> value, they will work, but with the checks, they do not get UPDATEd.
>
> Here is the call:
>
> BEGIN;
> UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND bdate
> IS NULL;
> UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE ProjID =
> '215';
> UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID =
> '215';
> COMMIT;
>
> As you can see, I am using 3 different checks and none of these are
> working. I know it's something simple, but what it is?
>
> Any ideas?
>
> Maybe the other question is, what defines "IS NULL" or "ifnull" or
> "coalesce"?
>
> thanks,
>
> josé
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
