check the key value before inserting data [modified]---ALREADY SOLVED---
-
let suppose i have table like this:
id name key
1 sam sam1
2 joy joy2name and key are inserted by user. Now whenever i am inserting new value, I need to check whelther I have that key exists or not. If yes, do nothing and if no, insert it. I know how to do it in dot net page but can I do this in sql query itself ? If yes, can anyone please help me..
suchita
modified on Wednesday, June 29, 2011 9:32 AM
-
let suppose i have table like this:
id name key
1 sam sam1
2 joy joy2name and key are inserted by user. Now whenever i am inserting new value, I need to check whelther I have that key exists or not. If yes, do nothing and if no, insert it. I know how to do it in dot net page but can I do this in sql query itself ? If yes, can anyone please help me..
suchita
modified on Wednesday, June 29, 2011 9:32 AM
Yes. You could try:
IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
BEGIN
INSERT INTO.....
ENDAs an aside: this may not be the case because I can only see two rows of sample data, but if the [keyname] value is a concatenation of [name] + [id] and [id] is an identity column, then you should always have a unique value in [keyname]. Hope that helps.
-
let suppose i have table like this:
id name key
1 sam sam1
2 joy joy2name and key are inserted by user. Now whenever i am inserting new value, I need to check whelther I have that key exists or not. If yes, do nothing and if no, insert it. I know how to do it in dot net page but can I do this in sql query itself ? If yes, can anyone please help me..
suchita
modified on Wednesday, June 29, 2011 9:32 AM
If uncrush is right then one has to ask why you are creating the KeyName field, it is redundant. When I have to check for existing keys, I return the record instead of using exists, then test if I got a record, that way I have all the information about the record. I always put this type of work into a stored procedure that returns the record, the UI may not even know if the records was inserted ot not.
Never underestimate the power of human stupidity RAH
-
let suppose i have table like this:
id name key
1 sam sam1
2 joy joy2name and key are inserted by user. Now whenever i am inserting new value, I need to check whelther I have that key exists or not. If yes, do nothing and if no, insert it. I know how to do it in dot net page but can I do this in sql query itself ? If yes, can anyone please help me..
suchita
modified on Wednesday, June 29, 2011 9:32 AM
Not knowing what database you are using, but you could also put a
UNIQUE CONSTRAINTon the keyname column. :)Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
let suppose i have table like this:
id name key
1 sam sam1
2 joy joy2name and key are inserted by user. Now whenever i am inserting new value, I need to check whelther I have that key exists or not. If yes, do nothing and if no, insert it. I know how to do it in dot net page but can I do this in sql query itself ? If yes, can anyone please help me..
suchita
modified on Wednesday, June 29, 2011 9:32 AM
SayamiSuchi wrote:
keyname ... inserted by user
Then why should it be unique? :confused: If there's something that needs to be unique, then it should be created/set by the system not the user. You already have
id, what more do you need? -
Not knowing what database you are using, but you could also put a
UNIQUE CONSTRAINTon the keyname column. :)Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
I dislike using these, they tempt the developer into programming by error, try and add the dupe and trap the error. Whereas checking first makes the constraint redundant an does not generate an error.
Never underestimate the power of human stupidity RAH
-
SayamiSuchi wrote:
keyname ... inserted by user
Then why should it be unique? :confused: If there's something that needs to be unique, then it should be created/set by the system not the user. You already have
id, what more do you need? -
I dislike using these, they tempt the developer into programming by error, try and add the dupe and trap the error. Whereas checking first makes the constraint redundant an does not generate an error.
Never underestimate the power of human stupidity RAH
-
SayamiSuchi wrote:
keyname ... inserted by user
Then why should it be unique? :confused: If there's something that needs to be unique, then it should be created/set by the system not the user. You already have
id, what more do you need?Take a list of usernames. Usernames should be unique, but there's usually an identity. The identity is for the system, the username for the user. Same goes for a filename; they need be unique, but you don't let the system choose it.
Bastard Programmer from Hell :suss:
-
Take a list of usernames. Usernames should be unique, but there's usually an identity. The identity is for the system, the username for the user. Same goes for a filename; they need be unique, but you don't let the system choose it.
Bastard Programmer from Hell :suss:
Eddy Vluggen wrote:
filename; they need be unique
Not necessarily.
-
PIEBALDconsult wrote:
If there's something that needs to be unique, then it should be created/set by the system not the user
So next time you signup for an online service your username will be
papsd9308skjfd9kdi32.Yeah, pretty much, at least I won't have to waste a half hour trying things and trying things always having it tell me it exists. Think of Animal House -- "Your name is Flounder. Your name is Pinto." Simple, saves the user a lot of needless trouble.
-
Eddy Vluggen wrote:
filename; they need be unique
Not necessarily.
-
I dislike using these, they tempt the developer into programming by error, try and add the dupe and trap the error. Whereas checking first makes the constraint redundant an does not generate an error.
Never underestimate the power of human stupidity RAH
Again depending upon the database and how the constraint operation is implemented, there may also be performance gains by using the constraint instead of coding it into a program. But your mileage may vary extensively. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
I disagree, on the same terms that I do not check whether a file is readonly/accesible/existing when I delete it.
Bastard Programmer from Hell :suss:
Eddy Vluggen wrote:
on the same terms that I do not check whether a file is readonly/accesible/existing when I delete it
If I am coding file I/O I certainly do!
Never underestimate the power of human stupidity RAH
-
Your new contract is located on the Z-share, and is called "contract.doc". Aw, there are two more files with the same name in there. :z
Bastard Programmer from Hell :suss:
That wouldn't be in the database, would it?
-
That wouldn't be in the database, would it?
-
That wouldn't matter, would it? :) Some people generally check every option, others trust in a form of exception-handling.
Bastard Programmer from Hell :suss:
Yes, it would.
-
Yes, it would.
Why does it matter to you? Do you make a difference between deleting a file from disk and a file from a database, from an exception-handling viewpoint? :) It's a personal preference; lots of experienced programmers tend to check specific whether a file (or record) exists and whether they've got the appropriate rights to delete it (from disk or database). I prefer to rely on exception-handling, has always worked quite well for me. Do you try-except, or do you return a customized boolean that consists of
Yes,NoandEFileNotFound?Bastard Programmer from Hell :suss:
-
Why does it matter to you? Do you make a difference between deleting a file from disk and a file from a database, from an exception-handling viewpoint? :) It's a personal preference; lots of experienced programmers tend to check specific whether a file (or record) exists and whether they've got the appropriate rights to delete it (from disk or database). I prefer to rely on exception-handling, has always worked quite well for me. Do you try-except, or do you return a customized boolean that consists of
Yes,NoandEFileNotFound?Bastard Programmer from Hell :suss:
I'm pretty sure none of that has anything to do with the original question.
Eddy Vluggen wrote:
a difference between deleting a file from disk and a file from a database
Yes. The database will likely return "0 records affected". The file system may simply say "ok, it's gone" even if it didn't exist. Either way, I don't care, just aslong as it's gone.
Eddy Vluggen wrote:
whether they've got the appropriate rights to delete it
That's a case where looking first may give inacurate information and you could wind up having to handle an Exception anyway, so I prefer to just try it and see.
-
I'm pretty sure none of that has anything to do with the original question.
Eddy Vluggen wrote:
a difference between deleting a file from disk and a file from a database
Yes. The database will likely return "0 records affected". The file system may simply say "ok, it's gone" even if it didn't exist. Either way, I don't care, just aslong as it's gone.
Eddy Vluggen wrote:
whether they've got the appropriate rights to delete it
That's a case where looking first may give inacurate information and you could wind up having to handle an Exception anyway, so I prefer to just try it and see.