Topic: [Query] Check for duplicate for beginners
Today, I would like to share a tip with beginners on how to make sure you are not inserting duplicate terms in your database. This is directly in your procedure, this is not a function you can call again and again, you'll have to re-write it each time for each procedure.
If you're looking for some function to do this, you can have a look at this thread by Tcoton : Function CheckDuplicate
It's very simple and supported by SQLite AND MySql.
Imagine you have a word in a Edit, for example a name, let's say "Mathias".
You want to add this customer to your database but there are so many off them already, that it's a hassle to review them all before saving.
You're going, before insertion in database, to query the table and see if there is already a customer named "Mathias", right ?
But what if the name is spelled "mathias", or "mathiaS"... different cases ?
Well, here is how to query the table to avoid duplicates :
var new_name : String; existing_name_count : Integer begin //What is the new name you want to save ? new_name := Form1.Edit1.Text; //Let's see if this name is alredy present by counting it's occurence existing_name_count := SQLExecute('SELECT COUNT(name) FROM customer WHERE upper(name)=upper("'+new_name+'")'); if existing_name_count <> 0 then begin This is a duplicate end else if existing_name_count = 0 then begin This is not a duplicate, we can save it end; end;
Did you notice the upper() in the query ? This way, you are comparing strings form the database and from your Form1.Edit1.Text, with the same case type.
You could also use lower() if you wish.
That's all for now, hope it will be useful.
PS : there might be other solutions, this is just the one I use.
Each time I get a divorce, I keep the house