Re: [sqlite] IF-THEN-ELSE sqlite
On Mon, Apr 4, 2011 at 12:26 PM, Simon Slavin wrote: > But he's combining two INSERTs into one. What I think he needs is first an > INSERT OR FAIL to possibly add a new person, and then an INSERT ... SELECT > which looks up that person's ID. I didn't see that in the original post. Did I miss it? But even so: INSERT ...; INSERT ... SELECT ... WHERE ... (SELECT EXISTS ); :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 4 Apr 2011, at 6:01pm, Nico Williams wrote: > On Mon, Apr 4, 2011 at 11:25 AM, Simon Slavin wrote: >> Probably not. Don't try to turn SQL into a procedural language. Do the >> SELECT that tells you whether the record exists and gives you the >> information you need if it does, then do whatever INSERTs you need to do. > > I agree with the first part. I don't agree with the second. SQL _is_ > a programming language. If you resort to using a procedural language > to do the things you don't know how to do in SQL then you're not > really meeting the spirit of your first recommendation. This is why I > like the INSERT ... SELECT ... WHERE > idiom: it's declarative, > and it uses SQL as a programming language with less glue needed from > the host language. But he's combining two INSERTs into one. What I think he needs is first an INSERT OR FAIL to possibly add a new person, and then an INSERT ... SELECT which looks up that person's ID. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On Mon, Apr 4, 2011 at 11:25 AM, Simon Slavin wrote: > Probably not. Don't try to turn SQL into a procedural language. Do the > SELECT that tells you whether the record exists and gives you the information > you need if it does, then do whatever INSERTs you need to do. I agree with the first part. I don't agree with the second. SQL _is_ a programming language. If you resort to using a procedural language to do the things you don't know how to do in SQL then you're not really meeting the spirit of your first recommendation. This is why I like the INSERT ... SELECT ... WHERE idiom: it's declarative, and it uses SQL as a programming language with less glue needed from the host language. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 4 Apr 2011, at 4:26pm, Marcelo Serrano Zanetti wrote: > About the "new" item, yes I do compare some specific fields and not the > primary key. For example name and surname of a person ... I look in the > database for such a person ... if yes I return her id ... if not I > include her and then return her id ... so I thought it would be more > efficient to construct a single query that does all the job avoiding > multiple queries. Probably not. Don't try to turn SQL into a procedural language. Do the SELECT that tells you whether the record exists and gives you the information you need if it does, then do whatever INSERTs you need to do. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 04/04/2011 05:16 PM, Robert Poor wrote: > @Marcelo: > > Going back to your original question: do you really only want to > insert one item at a time? If so, I think your question has been > answered reasonably well. > > BUT: if you have a large number of items, and you want to insert items > that aren't yet in the table, then you can do it efficiently in a > single query. Check the documentation for "INSERT OR IGNORE", i.e. > > http://sqlite.org/lang_insert.html > > Also, you say a new item is to be "inserted only if this item is not > yet in that table", but you haven't described how you discriminate a > new item from an existing item. Obviously you are not comparing > primary keys (since the new item won't have a primary key) -- do you > mean to compare all of the other fields? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Thx for answering Robert, In principle I want to include one at a time ... because I am parsing web sites. So after each parsing I insert the results in the database. About the "new" item, yes I do compare some specific fields and not the primary key. For example name and surname of a person ... I look in the database for such a person ... if yes I return her id ... if not I include her and then return her id ... so I thought it would be more efficient to construct a single query that does all the job avoiding multiple queries. I need that id to process other tables that is why I am doing one at a time. best -- Marcelo S Zanetti ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
@Marcelo: Going back to your original question: do you really only want to insert one item at a time? If so, I think your question has been answered reasonably well. BUT: if you have a large number of items, and you want to insert items that aren't yet in the table, then you can do it efficiently in a single query. Check the documentation for "INSERT OR IGNORE", i.e. http://sqlite.org/lang_insert.html Also, you say a new item is to be "inserted only if this item is not yet in that table", but you haven't described how you discriminate a new item from an existing item. Obviously you are not comparing primary keys (since the new item won't have a primary key) -- do you mean to compare all of the other fields? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 04/03/2011 09:05 PM, Petite Abeille wrote: > On Apr 3, 2011, at 3:18 PM, Marcelo Serrano Zanetti wrote: > >> It does not work in this way ... could somebody tell me please what is >> the correct sintax or whether this is possible at all. > As mentioned, SQL is not a procedural language, so, no. > > That said, you can achieve the same effect with two SQL statements called in > succession: > > (1) insert or ignore into table( item ) values( new ) [1] > (2) select itemID from table where item = new > > In other words, always try to create the new item, then select it. > > [1] http://www.sqlite.org/lang_insert.html > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users thx -- Marcelo S Zanetti __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On Apr 3, 2011, at 3:18 PM, Marcelo Serrano Zanetti wrote: > It does not work in this way ... could somebody tell me please what is > the correct sintax or whether this is possible at all. As mentioned, SQL is not a procedural language, so, no. That said, you can achieve the same effect with two SQL statements called in succession: (1) insert or ignore into table( item ) values( new ) [1] (2) select itemID from table where item = new In other words, always try to create the new item, then select it. [1] http://www.sqlite.org/lang_insert.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On Sun, Apr 3, 2011 at 3:49 AM, Marcelo S Zanetti wrote: > IF 1==SELECT COUNT(*) from table > WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE > INSERT INTO table (item) VALUES (new) INSERT INTO t (item) SELECT :new WHERE NOT EXISTS (SELECT item FROM t WHERE item = :new); Here :new is a parameter to be bound with sqlite3_bind_*(). Of course, it also seems like you could just INSERT OR IGNORE, as it seems likely that you want that item column to be a unique key (if not the primary key). There's no IF in SQL, and you can't embed an INSERT/UPDATE/DELETE inside a SELECT (so CASE won't help). But you _can_ have WHERE clauses that don't obviously relate to the rows being selected. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 03/04/2011, at 6:49 PM, Marcelo S Zanetti wrote: > I have an item to insert in the table which will be inserted only if this > item is not yet in that table otherwise I would like to return the item's key. > > like that > > IF 1==SELECT COUNT(*) from table > WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE > INSERT INTO table (item) VALUES (new) > > It does not work in this way ... could somebody tell me please what is the > correct sintax or whether this is possible at all. SQL is a set manipulation language, not a procedural language. So you write commands that affect a subset of data all at once. To accomplish your task, you'd write this: insert into Table (Item) select new where new not in (select item from Table); select ItemID from Table where Item = new; Furthermore, if you are returning the key for some more manipulation, it's best done in the same SQL call, rather than manipulated in your application code only to be re-injected into the SQL from which it came. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 04/03/2011 03:12 PM, Igor Tandetnik wrote: > Marcelo S Zanetti wrote: >> I >> have an item to insert in the table which will be inserted only if this >> item is not yet in that table otherwise I would like to return the >> item's key. >> >> like that >> >> IF 1==SELECT COUNT(*) from table >> WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE >> INSERT INTO table (item) VALUES (new) >> >> It does not work in this way ... could somebody tell me please what is the >> correct sintax or whether this is possible at all. > You use SQLite API to execute the three SQL statements, and use whatever > programming language your host application is written in to implement the > surrounding logic. Thank you Igor! I thought it could be organized in a single query ... like you said, the only way I managed to do it was using three separated queries. Best Marcelo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
Marcelo S Zanetti wrote: > I > have an item to insert in the table which will be inserted only if this > item is not yet in that table otherwise I would like to return the > item's key. > > like that > > IF 1==SELECT COUNT(*) from table > WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE > INSERT INTO table (item) VALUES (new) > > It does not work in this way ... could somebody tell me please what is the > correct sintax or whether this is possible at all. You use SQLite API to execute the three SQL statements, and use whatever programming language your host application is written in to implement the surrounding logic. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
Hi I would like to do the following I have an item to insert in the table which will be inserted only if this item is not yet in that table otherwise I would like to return the item's key. like that IF 1==SELECT COUNT(*) from table WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE INSERT INTO table (item) VALUES (new) It does not work in this way ... could somebody tell me please what is the correct sintax or whether this is possible at all. Thank You ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users