Re: [sqlite] Delete, sometimes doesn't seem to work ...

[John Machin]

On 8/02/2009 8:33 AM, Simon wrote:
> Difficult to say for sure, but it's possible the Indx of 0 were
> inserted with another type (ie, the string "0" and of course, 0 !=
> "0")

If the column is declared as integer (as the OP said) you need to try 
harder than '0' ... not trimmimg leading/trailing spaces will do the 
trick. Also remember the way that NULL behaves.

SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table foo (indx integer);
sqlite> insert into foo values(0);
sqlite> insert into foo values('0');
sqlite> insert into foo values('0.0');
sqlite> insert into foo values(null);
sqlite> insert into foo values(0.0);
sqlite> insert into foo values(' 0');
sqlite> insert into foo values('0 ');
sqlite> select typeof(indx), count(*) from foo group by typeof(indx);
integer|4
null|1
text|2
sqlite> select rowid, '<' || ifnull(indx, 'NULL') || '>' from foo where 
indx != 0 or indx is null;
4|
6|< 0>
7|<0 >
sqlite> select rowid, '<' || ifnull(indx, 'NULL') || '>' from foo where 
indx = 0;
1|<0>
2|<0>
3|<0>
5|<0>
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Delete, sometimes doesn't seem to work ...

[Stef Mientki]

thanks Simon and Igor

Simon wrote:
> Difficult to say for sure, but it's possible the Indx of 0 were
> inserted with another type (ie, the string "0" and of course, 0 !=
> "0")
>   
I guess that might have happened.
Igor,
I can't try it anymore, because I already deleted the records,
I'll try to rmemeber that for the next time.

cheers,
Stef
> Simon
>
> On Sat, Feb 7, 2009 at 4:28 PM, Stef Mientki  wrote:
>   
>> hello,
>>
>> I'm a very happy user of sqlite for about 2 years.
>> And as I'm happy for a  long time,
>> I forgot all tiny details and funny things.
>>
>> Besides that, I changed from Delphi to Python,
>> and now I'm in trouble :-(
>> So I might have version problems,
>> but that's not the case here,
>> as I've the same problem executing the statement below from within my
>> Delphi application.
>>
>> The following statement doesn't seem to work (rows are not removed)
>> DELETE FROM [_1_aap] WHERE Indx=0
>>
>> But selecting another value than zero, does work (rows are indeed removed)
>> DELETE FROM [_1_aap] WHERE Indx=1
>>
>> Indx is declared as an integer field.
>> In Delphi I'm using SQLite 3.3.8
>>
>> One other point, those rows with Indx=0 were inserted by a wrong
>> statement (can't remember exactly what)
>>
>> Anyone has a clue ?
>>
>> btw. for the moment I solved the problem by recreating the whole
>> database again.
>>
>> thanks,
>> Stef Mientki
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Delete, sometimes doesn't seem to work ...

[Igor Tandetnik]

"Stef Mientki"  wrote in
message news:498dfd12.7080...@ru.nl
> The following statement doesn't seem to work (rows are not removed)
> DELETE FROM [_1_aap] WHERE Indx=0
>
> But selecting another value than zero, does work (rows are indeed
> removed) DELETE FROM [_1_aap] WHERE Indx=1

What does this statement return:

select count(*) from [_1_aap] WHERE Indx=0;

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Delete, sometimes doesn't seem to work ...

[Simon]

Difficult to say for sure, but it's possible the Indx of 0 were
inserted with another type (ie, the string "0" and of course, 0 !=
"0")

Simon

On Sat, Feb 7, 2009 at 4:28 PM, Stef Mientki  wrote:
> hello,
>
> I'm a very happy user of sqlite for about 2 years.
> And as I'm happy for a  long time,
> I forgot all tiny details and funny things.
>
> Besides that, I changed from Delphi to Python,
> and now I'm in trouble :-(
> So I might have version problems,
> but that's not the case here,
> as I've the same problem executing the statement below from within my
> Delphi application.
>
> The following statement doesn't seem to work (rows are not removed)
> DELETE FROM [_1_aap] WHERE Indx=0
>
> But selecting another value than zero, does work (rows are indeed removed)
> DELETE FROM [_1_aap] WHERE Indx=1
>
> Indx is declared as an integer field.
> In Delphi I'm using SQLite 3.3.8
>
> One other point, those rows with Indx=0 were inserted by a wrong
> statement (can't remember exactly what)
>
> Anyone has a clue ?
>
> btw. for the moment I solved the problem by recreating the whole
> database again.
>
> thanks,
> Stef Mientki
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
When Earth was the only inhabited planet in the Galaxy, it was a
primitive place, militarily speaking.  The only weapon they had ever
invented worth mentioning was a crude and inefficient nuclear-reaction
bomb for which they had not even developed the logical defense. -
Asimov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Delete, sometimes doesn't seem to work ...

[Stef Mientki]

hello,

I'm a very happy user of sqlite for about 2 years.
And as I'm happy for a  long time,
I forgot all tiny details and funny things.

Besides that, I changed from Delphi to Python,
and now I'm in trouble :-(
So I might have version problems,
but that's not the case here,
as I've the same problem executing the statement below from within my 
Delphi application.

The following statement doesn't seem to work (rows are not removed)
DELETE FROM [_1_aap] WHERE Indx=0

But selecting another value than zero, does work (rows are indeed removed)
DELETE FROM [_1_aap] WHERE Indx=1

Indx is declared as an integer field.
In Delphi I'm using SQLite 3.3.8

One other point, those rows with Indx=0 were inserted by a wrong 
statement (can't remember exactly what)

Anyone has a clue ?

btw. for the moment I solved the problem by recreating the whole 
database again.

thanks,
Stef Mientki

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users