On 31 Oct 2017, at 10:21am, Eugene Mirotin <[email protected]> wrote:
> Hmm, I think I've found the solution:
>
> INSERT INTO search (rowid, question, answer, altAnswers, comments, authors,
> sources) SELECT id, question, answer, altAnswers, comments, authors,
> sources FROM questions WHERE obsolete IS NULL;
That looks like it should work, and the "INSERT … SELECT" form is very fast.
I do have a suggestion for something else: don’t use intentionally NULL values.
NULL means "unknown" or "no value" in SQL whereas what you mean is more like
TRUE and FALSE.
SQLite doesn’t have a BOOLEAN type. Instead the fastest, most compact way to
store booleans is to use 0 and 1. And if you store those values you can do
things like
… WHERE obsolete — equivalent to WHERE obsolete = 1
… WHERE NOT obsolete — equivalent to WHERE obsolete = 0
Unfortunately TRUE and FALSE are not reserved words, so you do have to do
things like
CREATE TABLE questions (…, obsolete INTEGER DEFAULT 0, …).
Using specific values 0 and 1 means that if you have a fault in your software
and end up with NULL values in your fields you know something definitely went
wrong.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
