On Sun, 18 Jun 2006 00:23:58 -0400, David D Speck wrote:
>Please pardon what may be a rank newbie question, but I'm just starting
>with SQL and have not seen this addressed in the tutorials that I've
>studied.
>I have to compile a single name and address list from many small list
>files. It is likely that many of the names will be present in more than
>one input list, but I only want to have one copy of them in the master
>SQL table.
>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.
