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
