October 31, 2006 at 11:51 am
Hi,
I was wondering if there is an easy way to search through a table for a match on Multiple fields. Lets say I have a record in the Price table for
Field, Value
Cost, 34.52
Preferred, 90.05
Standard, 90.05
Distributor, 111.25
Contractor, 125.50
List, 135.35
ID, ID-PRICE_1789
Before I insert a new record, I want to search to see if the above values already exist. If they do exist, I dont do an insert, if they dont exist, I would insert a new record with the new values. If the values exist I would get the ID number to use in another SQL statement.
Thanks for any help.
David
October 31, 2006 at 12:11 pm
DECLARE @id VARCHAR ( 20 ) -- or whatever datatype your id is
SELECT @id = ID
FROM table
WHERE Cost = @cost
AND Preferred = @preferred
... (etc)
IF @id IS NULL
INSERT table (Cost, Preferred, ...)
VALUES (@cost, @preferred, ...)
-------------------------------------------------------
Something like that what you're looking for?
October 31, 2006 at 12:29 pm
Interesting, except why would you need to get the ID from an existing row in the table, since you have specified that it is one of the search arguments? I think you need to be more specific about the business rules you are trying to implement because it makes a big difference on how to approach this.
Aaron's solution assumes facts not in evidence. For example, it is assumed that there are currently no duplicates in the table. If the select statement matches 2 or more rows in the table, it will fail.
jg
October 31, 2006 at 12:57 pm
The ID field is not one of the search criteria, I just need the value returned as in the statement above. I am inserting this ID field in another table as a pointer to the pricing in the Price table.
October 31, 2006 at 3:04 pm
Understood. That's not what you said originally, however...
"I want to search to see if the above values already exist. " and ID is up there.
Now, how are you going to come up with a new ID value if one is needed?
Also, are there currently any duplicates for the columns that you are using for the search? It would seem that you want to maintain uniqueness, so you should have a constraint to that effect.
Also, do you have/allow duplicates for ID? If not, then you should have a uniqueness or PK constraint on that column.
If you can guarantee uniqueness via constraints, then you could use code similar to what Aaron posted, but you still need to figure out how you are going to determine a new ID. And the ID column must not have NULL as a possible value because the code reles on a NULL being returned if there are no matches.
IOW, get all the rules figured out and then figure out how to write the code, not the other way around.
hth jg
November 1, 2006 at 1:33 pm
I realize I should have left the ID, ID-PRICE_1789 off the list, you are correct and I hope it wasnt too confusing. There are no duplicates for the ID field and that is already taken care of. I also will get the Last ID number and increment it for a new insert. I was just trying to get an idea of an easy way to see if a record already existed with multiple fields to check. I have several pieces to this project, and just wanted a simple solution to this part of it.
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply