DROP TABLE IF EXISTS TEXTS;
CREATE TABLE TEXTS (text_id int PRIMARY KEY, text ntext,last_update datetime DEFAULT
CURRENT_TIMESTAMP);
DROP TABLE IF EXISTS FTSTEXTS;
CREATE VIRTUAL TABLE FTSTEXTS USING fts5(text,content="TEXTS",content_rowid="text_id",tokenize=unicode61);
Insert into texts (text_id,text) values (1,'first'),(2,'second'),(3,'third'),(4,'fourth');
Insert into ftstexts (rowid,text) values (1,'first'),(2,'second'),(3,'third'),(4,'fourth');
update texts set text='5555' where text_id=2;
update ftstexts set text='5555' where rowid=2;
select text from texts where text_id=(select rowid from ftstexts where text match('second') limit 1);
Result is "5555"
After creating table and updating the value of row 2 to "5555" the query
select rowid from ftstexts where text match('second') limit 1
Returns 2:
How can the FTS table be populated to exclude the term "second" from FTS search?
The query was run on SQLITEStudio 3.2.1
External content FTS5 tables don't support updates. You have to delete the old contents of a row and insert the new contents as separate steps.
The documentation includes some sample triggers to automate keeping such a table in sync with the table that holds the actual data.
You might make an update trigger something along the lines of
CREATE TRIGGER texts_au AFTER UPDATE ON texts BEGIN
INSERT INTO ftstexts(ftstexts, rowid, text) VALUES('delete', old.text_id, old.text);
INSERT INTO ftstexts(rowid, text) VALUES (new.text_id, new.text);
END;