if record exists : UPDATE, otherwise INSERT

[plook]

Hi all,

I have a newbie question in MS SQL 7

Let's start with the following statement

UPDATE cyber_pricing
SET pricing = 1,
description = 'blah blah',
stock = 'NO'
WHERE id = 12345

This will update the record id "12345" only if it exist,how could one insert the record if it does not exist ?

I mean, is there a better way to do it instead of doing a SELECT of that id before, count the record, if the count is 0 do an INSERT, otherwise (if count IS 1) do an UPDATE ?

Thanks

Plook

 

[r937]

"is there a better way to do it instead of doing a SELECT of that id before, count the record, if the count is 0 do an INSERT, otherwise (if count IS 1) do an UPDATE?"

well, i dunno about "better" ...

1. do the insert
2. do the update
3. ignore any error messages you get

but that won't work if ID is an IDENTITY column

see, the problem revolves around your primary key

how do you know if "the record" exists? obviously, this can be answered only with reference to meaningful values in some column(s)

if your table has an IDENTITY primary key, you could keep adding the same "record" over and over, and each one would get a new value of the primary key, and therefore satisfy everybody's criteria for integrity and uniqueness of primary key... except that your table would have duplicate "records"

when the primary key is an IDENTITY column, it's a surrogate key, and the "real" key is somewhere else in the row

so what you need to do is declare a UNIQUE constraint on the "real" key

i'm not knocking the common practice of using an IDENTITY column as the primary key, by the way -- it makes sense in a lot of situations

for example, in an employee table, the "real" primary key might be first name plus last name plus date of birth, but you do not want to repeat those columns as foreign keys in all the tables that reference the employee table

if i haven't answered your question, plook, please post again and say what your PK is and where the "is it an insert or is it an update" data is coming from

rudy

http://rudy.ca/

 

[plook]

Thank you for your reply rudy.

this table does not have any PK. The ID field could "act" as a PK since there will be only one (if any) instance of that ID in the field.

And regarding doing an Insert, then an Update and ignore the error messages... well.. this query is done through a web interface (in ColdFusion), so, i'm not too keen on displaying error messages on purpose.

Do you think doing a SELECT of the field, count the results and do the right query accordingly is a bad idea ? This query won't be done several times a day...so even though it could be slow, it does not really matter.

But, if there is a better way (or more efficient) I would like to know...

Thanks for your help

Plook

 

[r937]

it doesn't matter if ID is not the PK (or even if you have a PK), but is ID an IDENTITY column? that's what will decide how you approach this

as for ignoring the error, that's really easy to do in CF, i can talk about that when you reply, although it may not be necessary, depending on what the ID column actually contains as values

 

[plook]

no, ID is not an IDENTITY column and.. the table does not have any. And the ID column contains only numerical values. INTEGER to be more precise.

 

[r937]

okay, that's great

now the problem is slightly different

is this information coming in from a form on a web page?

it is common for a form to act both as the way to capture new records, and as the update form for existing records

this means the person entering the ID number of new records (you?) must pick a new number that is known to be new, since you do not want your sql to automatically update the existing record if you happen, by accident, when trying to insert a new record, to type the ID number of an exiting record, because the new values will wipe out the existing record

therefore i would advise that you don't do what you were asking how to do

instead, make your web form tell your coldfusion script whether the Update or the Insert button was pressed, and use that distinction to decide whether to update or insert, not whether the ID exists or not

does that make sense?

 

[tlbroadbent]

This is a common and quite efficient way to handle the Insert or Update decision. It is efficient if the ID column is indexed.

If Exists
(Select * From cyber_pricing
WHERE id = 12345)
UPDATE cyber_pricing
SET pricing = 1,
description = 'blah blah',
stock = 'NO'
WHERE id = 12345
Else
Insert cyber_pricing(ID, Pricing, Description, Stock)
values (12345, 1, 'blah blah', 'NO')

However, as r937 says, you need to be cautious with this type of approach. If the numbers are strictly contolled and you don't allow just any user to update records from your web page, you can make this approach work. I would recommend that the ID column be a primary key or at least a unique index. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[r937]

my caution goes even deeper than that, terry

even if it's a single, trusted, careful individual who is submitting the data, we must protect against inadvertent error (a.k.a. "sudden loss of data integrity" or "uh oh, where did the jones data go?!!&quot

neither primary key nor unique index addresses this problem

it requires a distinction between intended actions, such as using different submit buttons (there are other ways, like using separate web pages and separate forms, but different submit buttons is easiest)

so if the intent is insert, you should never update

if the intent is update, you should never insert

i guess i wasn't really answering the question asked, but murphy is my cousin and i wanted to suggest not doing it that way in the first place