On Mon, Dec 30, 2013 at 5:24 AM, Raheel Gupta <raheel...@gmail.com> wrote:
> Hi, > > I have the following tables : > CREATE TABLE checksums > (i INTEGER,c VARCHAR(16) PRIMARY KEY) ; > > CREATE TABLE data > (i INTEGER, data BLOB, isUnique INTEGER) > > My application needs to insert 1000s of rows/second into the data table > hence I use transactions and prepare statements. > > Now is it possible that while inserting (using transactions) in the > Checksums table when there is a CONFLICT as per the column "c" I update > table "data" column isUnique with the corresponding ID "i" from table > checksums. > > Something like : > INSERT INTO checksums (1000, "abcdefghijklmnop") ON CONFLICT IGNORE BUT > UPDATE data SET isUnique = OLD.checksums.i > The wordcount.c test program (see http://www.sqlite.org/src/artifact/9915e06cb) demonstrates four different ways to do this. One example: sqlite3_exec("INSERT OR IGNORE INTO ....;"); if( sqlite3_changes()==0 ){ sqlite3_exec("UPDATE ..."); } The wordcount.c test program does the above using prepared statements and exceeds 348,000 inserts+updates per second on my desktop when compiled using GCC version 4.8.1 and -Os. See http://www.sqlite.org/src/artifact/9915e06cb?ln=432-445 for the inner loop. > > I know the above ON CONFLICT statement is wrong but I need a similar > functionality. > > Is this possible with HOOKS / triggers or anything at all ? > This will be a great feature if its there. > > Regards > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
