Topic: [Query] Check for duplicate for beginners
Hello everyone,
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.
Cheers
Mathias
PS : there might be other solutions, this is just the one I use.
Each time I get a divorce, I keep the house
Zaza Gabor