Re: [sqlite] how to compose the sql sentence?
Thank you very much! you told me how to do. but I missed the "... defined as UNIQUE ...". Actually my question is just solved by two step: First - CREATE UNIQUE INDEX i_recdata ON rec_data (num, di, time1); Second - INSERT OR REPLACE INTO rec_data (num, di, data, time1, time2, format) VALUES (12, '1290', '732e4a39', 8323000, 8323255, 22); 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 > > -- View this message in context: http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23883819.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
Re: [sqlite] how to compose the sql sentence?
Assumptions : Your database scheme contains this declarations CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1 INTEGER, time2 INTEGER, format CHAR(1) ); CREATE UNIQUE INDEX i_data ON data ( num, di, time1 ); You want to do : A) Insert or Replace without looking at the previous value if one existed : *** INSERT OR REPLACE INTO data (num, di, data, time1, time2, format) VALUES (12, '1290', '732e4a39', 8323000, 8323255, 22); B) You want to see if an entry exists read that and edit it manually : ** in this case it is best to search also for the hidden rowid which simplifies updates SELECT rowid, num, di, data, time1, time2, format FROM data WHERE num=12 AND di='1290' AND time1=8323000 ; depending on the resultset : B1) there was a result you'll get as the first column a rowid (lets say it was 894)then update UPDATE data SET data='732e4a39' WHERE rowid=894 ; B2) there was no result set and you want to insert a new one INSERT INTO data (num, di, data, time1, time2, format) VALUES (12, '1290', '732e4a39', 8323000, 8323255, 22); Good luck ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compose the sql sentence?
On 4 Jun 2009, at 7:39am, liubin liu wrote: > INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290', > '732e4a39', 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? Here are 6 values: > VALUES (12, '1290', > '732e4a39', 8323000, 8323255, 22); Here are 3 columns: > (num, di, time1) It does not know which column to put which value in. If you give it 6 values you must list 6 columns. Value 1 goes into column 1, value 2 goes into column 2, an so on. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compose the sql sentence?
Hi, what do you mean by "command"? The command line tool takes commands entered by the user, such as SQL - statements. These do not have returncodes. If an error occurs, the command line tool will print out the error message. The functions of the sqlite3 library have returncodes, but they can not be called directly on the command line. Martin liubin liu schrieb: > 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', >>> '732e4a39', 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 >> >> >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compose the sql sentence?
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', >> '732e4a39', 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
Re: [sqlite] how to compose the sql sentence?
Yeah! Thank you very very much! this way could get my goal. but I am afraid the sql will cause some sqlite3 err? 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', >> '732e4a39', 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...-tp23845882p23865605.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
Re: [sqlite] how to compose the sql sentence?
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', > '732e4a39', 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
Re: [sqlite] how to compose the sql sentence?
column name and table name is my fault, :) the sql "INSERT OR REPLACE INTO table () VALUES ()" is equal with "INSERT INTO table VALUES()" so It can't achieve my goal. Harold Wood Meyuni Gani wrote: > > Well you have a column named data and a table named data, but the biggest > issue is you only supplied 3 column names but in the values list you have > 6 values. > > > > --- On Thu, 6/4/09, liubin liu <7101...@sina.com> wrote: > > > From: liubin liu <7101...@sina.com> > Subject: Re: [sqlite] how to compose the sql sentence? > To: sqlite-users@sqlite.org > Date: Thursday, June 4, 2009, 2:39 AM > > > > 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', > '732e4a39', 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 >> >> > > -- > View this message in context: > http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23864558.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 > ___ > 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...-tp23845882p23865368.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
Re: [sqlite] how to compose the sql sentence?
Well you have a column named data and a table named data, but the biggest issue is you only supplied 3 column names but in the values list you have 6 values. --- On Thu, 6/4/09, liubin liu <7101...@sina.com> wrote: From: liubin liu <7101...@sina.com> Subject: Re: [sqlite] how to compose the sql sentence? To: sqlite-users@sqlite.org Date: Thursday, June 4, 2009, 2:39 AM 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', '732e4a39', 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 > > -- View this message in context: http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23864558.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compose the sql sentence?
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', '732e4a39', 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 > > -- View this message in context: http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23864558.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
Re: [sqlite] how to compose the sql sentence?
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
Re: [sqlite] how to compose the sql sentence?
look up the insert or replace statement, http://www.sqlite.org/lang_insert.html --- On Wed, 6/3/09, liubin liu <7101...@sina.com> wrote: From: liubin liu <7101...@sina.com> Subject: [sqlite] how to compose the sql sentence? To: sqlite-users@sqlite.org Date: Wednesday, June 3, 2009, 2:05 AM 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. -- View this message in context: http://www.nabble.com/how-to-compose-the-sql-sentence--tp23845882p23845882.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to compose the sql sentence?
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. -- View this message in context: http://www.nabble.com/how-to-compose-the-sql-sentence--tp23845882p23845882.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
