Hi, First, you have to declare the index as unique:
CREATE UNIQUE INDEX i_data ON data (num, di, time1); or - depending on your database design, declare a primary key with these three fields. Second, the error message says it all: You supplied three column - names, but 6 values. Martin liubin liu schrieb: > Thank you a lot! > > > I created a table: > CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1 INTEGER, > time2 INTEGER, format CHAR(1) ); > > and create a index: > CREATE INDEX i_data ON data (num, di, time1); > > I want to do: > first tell whether there is a record in the table "data" according to the > index "i_data". > to update the record if there is a record; > to insert the record if there isn't any record. > > > > when I run the sql: > INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290', > '732e4a390000', 8323000, 8323255, 22); > > the sqlite3 report a error: > SQL error: 6 values for 3 columns > > Does It mean the method isn't the right way? > > > > > Simon Slavin-2 wrote: > >> On 3 Jun 2009, at 7:05am, liubin liu wrote: >> >> >>> the first step is to tell if there is the data in the table. >>> if the answer is not, I want to insert a row of data into the table >>> if the answer is yes, I need to update the row of data acccording to >>> the >>> data inputting from me. >>> >> INSERT OR REPLACE INTO table (columns) VALUES (values) >> >> This will use the columns and indices you have already defined as >> UNIQUE to decide whether it should INSERT a new row or REPLACE an >> existing one. So take care in creating UNIQUE columns or a UNIQUE >> index that does what you want. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
