sqlite3 delete does not delete everything?

Posted by Skand on Stack Overflow See other posts from Stack Overflow or by Skand
Published on 2010-03-11T05:07:45Z Indexed on 2010/03/11 5:13 UTC
Read the original article Hit count: 296

Filed under:

Whats going on here? I would expect the following delete to delete everything from the table. Is there a fundamental mis-understanding of how sqlite3 behaves on my part?

sqlite> .schema

CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl INTEGER, PRIMARY KEY(ip_domain, answer, ttl));

sqlite> select count(*) from ip_domain_table where ttl < 9999999999 ;

1605343

sqlite> pragma cache_size=100000; delete from ip_domain_table where ttl < 9999999999;

sqlite> select count(*) from ip_domain_table where ttl < 9999999999 ;

258

Q: Why does the count show "258"? Shouldn't it be 0 instead?

If I do this instead, it deletes all the entries as expected.

sqlite> select count(*) from ip_domain_table;

1605343

sqlite> pragma cache_size=100000; delete from ip_domain_table;

sqlite> select count(*) from ip_domain_table;

0

© Stack Overflow or respective owner

Related posts about sqlite3