CODEWITHSUNDEEP
androidsqlitefull-text-search
Andrey Rankov
Andrey Rankov

Reputation: 2090

INSERT or REPLACE into FTS table

Here is my database structure

CREATE VIRTUAL TABLE Products_fts USING fts4(PrdID, ConcatSearchField)
CREATE UNIQUE INDEX CategoryProduct_MM_CatID_PrdID_idx ON CategoryProduct_MM(CategoryID,PrdID)
CREATE INDEX CategoryProduct_MM_CatID_idx ON CategoryProduct_MM(CategoryID)
CREATE INDEX CategoryProduct_MM_PrdID_idx ON CategoryProduct_MM(PrdID)

I need to update it from another table with the same structure. 

Insert or Replace into Products_fts select * from mydb.Products_fts

Data is inserted, but it's duplicated. How to do INSERT OR REPLACE?

Upvotes: 0

Views: 701

Answers (1)

Andrey Rankov
Andrey Rankov

Reputation: 2090

FTS table contain hidden rowid field, that can be used to perform replace

INSERT OR REPLACE 
INTO Products_fts (rowid, PrdID, ConcatSearchField) 
SELECT PrdID AS rowid, PrdID, ConcatSearchField FROM mydb.Products_fts

Upvotes: 4

Related Questions