On Sun, Jun 18, 2006 at 01:16:35AM -0400, C.Peachment wrote:
> On Sun, 18 Jun 2006 00:23:58 -0400, David D Speck wrote:
> >What would the most elegant way be to insert a name and address entry
> >into the main table ONLY if it is not already there? I could see doing
> >a SELECT WHERE lname = new_lname AND fname = new_fname, AND street =
> >new_street, etc, and then aborting the INSERT if the SELECT returns a
> >match.
>
> >I just wondered if there was a neater way to accomplish this?
>
> You could declare each column in the table to be UNIQUE and let the
> database report each duplicate. For example:
>
> SQLite version 3.3.6
> Enter ".help" for instructions
> sqlite>create table test (
> ...>lname varchar(60) unique,
> ...>fname varchar(60) unique,
> ...>street varchar(60) unique);
> sqlite>insert into test values ('a', 'b', 'c');
> sqlite>insert into test values ('a', 'b', 'c');
> SQL error: column street is not unique
> sqlite>
>
> In a program, you might want to catch the error and report it.
I have a related question. Suppose I have a table containing rows each with
values and a counter. If the new row is unique in the table it should be
INSERTed and the counter set to 1; otherwise the counter for the matching
row should be incremented. It's a classic data reduction procedure.
As most of the operations will be UPDATEs I want to do something like
UPDATE ... if failed INSERT...
but I can't see an efficient way to express that in SQL understood by
sqlite.
Thanks,
