Most efficient method: insert values from list 1

[thedaver]

I'm not a DBA, so I'm afraid my question will be a bit simple... sorry and help appreciated!

I have a table:
id - primary key
value - varchar

From time to time I need to insert rows where the values come from a web form. I don't know which submitted values might be duplicates. So I need to figure out the most efficient method for a transaction that accomplishes:

"Insert values from the submitted list that are not yet present in that field".

So far that feels like a two-stage query - something like "find those that are in the list" and then parse the submitted items for those that didn't match. But that sounds terribly heavy for what should be simple.

HELP!?


D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[TonyGroves]

You could use:
[tt]
INSERT IGNORE tblname (id,value) VALUES ...
[/tt]
That will discard any new records whose id's are already in the table.

 

[thedaver]

Tony,
my 'id' field is "not null primary key auto_increment"

can I use

INSERT IGNORE tblname (value) VALUES ...

and get the expected result?

I've been thinking about it, and I may not have a need for an id for primary key since I'm making the 'value' unique.

Perhaps that's what's clouding my thinking?

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[TonyGroves]

INSERT IGNORE won't work there, since by definition there won't be any duplicate id values. If you want to ensure uniqueness in the value field, then you need to have a unique index (or primary key) on that field. And if the "id" field is not needed, there's no reason to keep it.