I think I already know the answer to this...but is it possible to append text to an FTS row without doing some sort of subselect?
Even with the subselect what;s the best way to do this? I seem unable to find a single-liner that works. sqlite> create virtual table data using fts4(content text); sqlite> insert into data values('one two'); sqlite> insert or replace into data(content) select content||' three four' from data where docid=1; The docid=1 doesn't work the way I want and I can't seem to figure out how to put in an "as" clause to make it work. Should end up with just one record but, of course, get 2. This doesn't work sqlite> insert or replace into data(content) select content||' three four' from data as c where docid=1; sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; PRAGMA writable_schema=ON; INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)VALUES('table','data','data',0,'CREATE VIRTUAL TABLE data using fts4(content text)'); CREATE TABLE 'data_content'(docid INTEGER PRIMARY KEY, 'c0content'); INSERT INTO "data_content" VALUES(1,'one two'); INSERT INTO "data_content" VALUES(2,'one two three four'); CREATE TABLE 'data_segments'(blockid INTEGER PRIMARY KEY, block BLOB); CREATE TABLE 'data_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx)); INSERT INTO "data_segdir" VALUES(0,0,0,0,0,X'00036F6E6503010200000374776F03010300'); INSERT INTO "data_segdir" VALUES(0,1,0,0,0,X'0004666F75720302050000036F6E650302020000057468726565030204000102776F03020300'); CREATE TABLE 'data_docsize'(docid INTEGER PRIMARY KEY, size BLOB); INSERT INTO "data_docsize" VALUES(1,X'02'); INSERT INTO "data_docsize" VALUES(2,X'04'); CREATE TABLE 'data_stat'(id INTEGER PRIMARY KEY, value BLOB); INSERT INTO "data_stat" VALUES(0,X'020619'); PRAGMA writable_schema=OFF; The "as" clause doens't seem to work in this context. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users