Re: [sqlite] IF-THEN-ELSE sqlite

[Nico Williams]

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

[Simon Slavin]

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

[Nico Williams]

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

[Simon Slavin]

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

[Marcelo Serrano Zanetti]

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

[Robert Poor]

@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

[Marcelo Serrano Zanetti]

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

[Petite Abeille]

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

[Nico Williams]

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

[sqlite] IF-THEN-ELSE sqlite

[Marcelo Serrano Zanetti]

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

Re: [sqlite] IF-THEN-ELSE sqlite

[BareFeetWare]

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

[Marcelo Serrano Zanetti]

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

[Igor Tandetnik]

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

[Marcelo S Zanetti]

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