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