Avoid duplicating records

[mentalhard]

How do i insert some values in one table but only if there is not having sucha values .. e.g. (pseudo code)

insert into table1 (name) values('myname') but if table1 not having name 'myname'

Thanks

 

[Arg81]

you mean into a field other than your primary key?

 

[cjard]

How do i insert some values in one table but only if there is not having sucha values .. e.g. (pseudo code)

insert into table1 (name) values('myname') but if table1 not having name 'myname'

Thanks

You dont bother.. just try inserting it anyway. If you set your PK up correctly, then the insert will fail

 

[mentalhard]

No no guys i guess my question was not well formed. Actually i thought more about something like following:

Dim strSQL as String = "insert into table1 ([name]) select '" & txt1.text & "' where not exists(select [name] from table1 t2 where [name]='" & txt1.text & "')"

This make sense to me but i still need to test it.

Thanks anyway

 

[cjard]

You dont write SQLs that way. For info, see my PQ link in my signature

Now think about this:

For a long time (longer than you have been alive, I guarantee) there has been a need to avoid duplicate records in a table. This is already implemented by every database vendor in the world and is called UNIQUE INDEX. Primary key is an often used example of unique index.
If you dont index the column you search on, then search time will grow linearly with the number of records in the table. If you do index it, then you might was well unique index it and the database itself will prevent any duplicate records.


Please tell me what it is about your solution here, that is better than the thousands of hours and dollars Microsoft/Oracle/Whoever has put into developing unique constraint systems?

I'd love to know your thinking..

 

[Arg81]

As Cjard says!

Although it's not what you asked for, I have a similar issue in one of my projects where I need to check a table, and look to see whether in any of the rows, the two columns values exist or not.
If a row exists with those values, it warns the user, who can still insert the row (I just had to add validation).

But seeing as you don't want the duplicate, then it should be indexed, so that if a row with that name already exists, it wont add another...

 

[cjard]

But seeing as you don't want the duplicate, then it should be indexed, so that if a row with that name already exists, it wont add another...

In oracle we have legendary statements like MERGE, which work in Upsert fashion, though these still need indexes to work effectively and hence the sense is in making it a unique index.. I used to beat my head on a brick wall with some people, really trying to guide them to the best ways of doing things but I guess the enormity of the task of saving the world has overcome me, and now if someone doesnt wanna listen.. Well, let em crack on doing it wrong

 

[mentalhard]

Too much theory for me. I am not that advance programmer to analize the best approach and stuff like that.
It would be far better if you post some SQL statement presenting the best approach how to insert data into database/table (mySQL) but only if there not exists a record with the value taken from the txt1.text

However even if you keep convincing me to read more about that i want you to know that i really appreciate your reply and willling to help in a right way.

Thanks

 

[InertiaM]

In a nutshell, what's being said is :-

Dim strSQL as String = "insert into table1 ([name]) values ('" & txt1.text & "')"

Put error checking on and execute. If no errors, it's a unique record. If it fails, then a record already exists and it didnt insert another.

 

[cjard]

In a nutshell, what's being said is :-

Dim strSQL as String = "insert into table1 ([name]) values ('" & txt1.text & "')"

Put error checking on and execute. If no errors, it's a unique record. If it fails, then a record already exists and it didnt insert another.

Please dont advise newbies, (and definitely people so hard to advise as mentalhard) to write SQLs like that. Read the PQ link in my signature for a set of clear reasons why that's bad, bordering on lame, with no redeeming features at all

 

[cjard]

It would be far better if you post some SQL statement

When I read that I see:

"I'm lazy. I dont want to learn, or even listen to what you have to say. Please give me the answer to my homework assignment so I can get a mark and never touch databases again, because I dont even know why I took the module"

Sorry dude.. Not what I'm about.

 

[InertiaM]

Please dont advise newbies, (and definitely people so hard to advise as mentalhard) to write SQLs like that. Read the PQ link in my signature for a set of clear reasons why that's bad, bordering on lame, with no redeeming features at all

Sorry cjard, I disagree.

I agree that PQ are the best long-term solution, but when testing, I believe it is better to write the whole SQL string. There are two reasons for this. Firstly, you can check the actual SQL before it is executed by (for example) writing it to the console. Secondly, you can test it in a program such as Access and see what it does. You cant do that with PQ.

 

[mentalhard]

InertiaM thanks for the try but it is not what i am looking for. However cjard you seems a bit rude with forcing me to learn your theory. You insist even if i kindly told you that i am not interested. If you have no intention to help at least do not insult me by calling me lazy and such.
I just saw your profile and that vocabular is not appropriate with your age.
Sorry cuz i had to say that but, you actually didn't help me at all.
All you have done was to make me learn from your thread. Thanks but, no. I don;t want.
I don;t need that help anymore. I found solution that is absolutelly Ok for my "home work".

It would be nice if you don't bother with your thread anymore. Thanks!!!

 

[Arg81]

I think you misread into the post. He wasn't calling YOU lazy, but the way you asked

It would be far better if you post some SQL statement presenting the best approach how to insert data into database/table

comes across that you want someone to provide the code for you without you working it out for yourself.

Do you realise how many students come on here begging for help for their projects because they can't actually do the work that is required of them?

forcing me to learn your theory

It's not his theory, it's the Microsoft theory! Too many people in VS2005 still do things the old way and don't want to change habit. When we try to provide help to them they get all defensive and "it has to be done the code way" - when in fact using the GUI and Wizards actually provides the code anyway.
If it was his theory, why do his links take you to the MSDN pages, unless Cjard actually owns and runs them himself?

Too much theory for me. I am not that advance programmer to analize the best approach and stuff like that.

So how do you intend to learn database programming? The best way to learn is not for someone to give you the answer straight up BUT point you in the direction you need to go.

 

[mentalhard]

I will no quote your statements taking them from context but let me just make one note if you don't mind

It's not his theory, it's the Microsoft theory!

Then why he repeats that at all?

And after all you didn't help me. It could matter the approach you use to help people offering some MS theory but anyway you didn't help me (altrough i have resolved the problem already).

Correct me if i am wrong!? And please no hard feelings. You spend too much engery on criticism than on real helping.