Indeed, stupid typo in my sql. Sorry for the noise and the lack of a subject
on the subject line in my first email.
David
--- On Tue, 10/27/09, Pavel Ivanov <paiva...@gmail.com> wrote:
> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] (no subject)
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Tuesday, October 27, 2009, 3:26 PM
> Something screwed up in your
> application:
>
> sqlite> create table assets (Code Text, Acct1 Text,
> Acct2 Text);
> sqlite> insert into assets values ('C0', 'name1',
> 'name2');
> sqlite> select * from assets;
> C0|name1|name2
> sqlite> insert into assets select 'C0', 'name1', 'name3'
> where not
> exists (select 1 from assets where Code='C0' and
> acct1='name1');
> sqlite> select * from assets;
> C0|name1|name2
> sqlite>
>
>
> Pavel
>
> On Tue, Oct 27, 2009 at 2:44 PM, David Bicking <dbic...@yahoo.com>
> wrote:
> > I have a table ASSETS with fields (Code Text, Acct1
> Text, Acct2 Text).
> > (There are other fields, and the primary key is a
> combination of 5 columns)
> >
> > For one code ('C0') I want to implement a rule that if
> I attempt to insert a combination of 'C0'/Acct1/Acct2 it
> will be ignored if the first two keys are already in the
> table. (for non-'C0' codes, this rule doesn't apply.)
> >
> > select * from assets;
> > Code Acct1 Acct2
> > 'C0' 'name1' 'name2'
> >
> >
> > insert into assets values('C0', 'name1', 'name3');
> -- ignore
> > insert into assets values('C0', 'name3', 'name4');
> -- succeed
> > insert into assets values('C0', 'name1', 'name2');
> -- ignore
> > insert into assets values('C1', 'name1', 'name2');
> -- succeed
> >
> > I tried:
> >
> > insert into assets select 'C0', 'name1', 'name3' where
> not exists (select 1 from assets where Code='C0' and
> acct1='name1');
> >
> > but it went ahead and inserted the row anyway. My hope
> was that since the where clause was false, the select would
> return nothing and thus nothing would be inserted.
> >
> > Is there a way I can craft the insert to do this, or
> do I need to set up a trigger to ignore the insert? If the
> latter, how should the trigger read?
> >
> > Thanks,
> > David
> > _______________________________________________
> > 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
