and I think of another question: how to know the return value of a command IN the command-line mode of sqlite3?
Martin Engelschalk wrote: > > 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 > > -- View this message in context: http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23865786.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
