> When you post on threads, please don't use the

"Digest"
subject.

> /heLen

Yes, Ma'am :-)

(Actually that was a mistake I couldn't prevent, because I am
not much used
to interacting with newsgroups by emails, and I am receiving a
Daily Digest.
I shall try to minimize such things in future, I know it makes
it more
difficult to locate and relate a particular post.)

I shall try to summarize my understanding of this issue here.
Please excuse
me and bear with me for repeating whatever has already been
said, because I
want to ensure I have not made any mistakes in understanding or
interpreting
the different suggstions and advice I have received so far.
(And

The problem: I want to create a stored procedure that will
either INSERT or
UPDATE a record into a table, based on whether the primary key
exists or
not. Let's also assume that the table is indexed on the column
being used
in the WHERE clause (which for this example is the primary key).
The method
which I have followed till now is:


1.
SELECT COUNT(*) FROM <table> WHERE <condition> INTO Var
IF Var = 0 THEN
INSERT ...
ELSE
UPDATE ... WHERE <condition>

The above code will traverse the particular index and then
return a count of
matching records. Then a condition will be evaluated and either
an INSERT
or an UPDATE operation will take place. Again, for the UPDATE
statement, it
will have to traverse the index to find all matching records, so
the index
is traversed twice.

Step 1: Traverse the index and fetch matching records.
Step 2: Evaluate a condition
Step 3: Execute either UPDATE or INSERT based on the evaluation
result.
Step 4: Traverse the index again, if UPDATE is performed.


2.
UPDATE .... WHERE <condition>
IF (ROW_COUNT = 0) THEN
INSERT ...

I think the UPDATE action will have to traverse through the
index to see if
any records match the WHERE clause. So this operation is
similar to the
traversal for a COUNT. After the UPDATE action has been
executed, there is
a condition on the ROW_COUNT variable. After the condition has
been
evaluated, an INSERT is carried out. Practically since this is
going to be
executed on the PK of the table, at no point of time would you
find the
UPDATE and INSERT taking place for the same PK, so this is again
similar to
the above IF ... ELSE, meaning either an UPDATE or an INSERT
will be carried
out (I know this may be bad practice in the long run, but I
think it's valid
for the case in question)

Step 1: Traverse the index to execute the UPDATE.
Step 1a : Maybe updating the ROW_COUNT variable should be
counted as a
separate step? Is it significant?
Step 2: Evaluate a condition (on ROW_COUNT)
Step 3: Perform an INSERT if evaluation result is True.


3.
IF (EXISTS(SELECT 1 FROM <table> WHERE <condition>)) THEN
UPDATE ... WHERE <condition>
ELSE
INSERT ....

I think the SELECT statement will be executed first, in order to
evaluate
the EXISTS() clause. Again, this SELECT statement is going to
go through
the whole index, so this operation is similar to the above two.
After the
SELECT statement has been executed, it will have returned a
cursor that
contains 1 for each matching record. Once the SELECT statement
returns, the
EXISTS() will be evaluated, which is the second step similar to
the above
solutions. Then, depending upon the outcome of the test, either
an UPDATE
or an INSERT will be carried out. However, in case of the
UPDATE, I think
it will again traverse the index, which means the index will be
traversed
twice.

Step 1: Traverse the index for the SELECT statement and fetch 1
for each
matching record.
Step 2: Evaluate the EXISTS() clause based on the result set of
the SELECT
statement.
Step 3: Execute either UPDATE or INSERT based on the evaluation
result.
Step 4: Traverse the index once again, in case of executing an
UPDATE.


4.
DECLARE VARIABLE MY_PK_VALUE INTEGER
MY_PK_VALUE = NULL
FOR SELECT <pk> FROM <table> WHERE <condition> INTO
MY_PK_VALUE AS
CURSOR TMPCURSOR
DO
UPDATE <table> SET ...
IF (MY_PK_VALUE IS NULL) THEN
INSERT .....

If we ignore the declaration and initialization of the variable
MY_PK_VALUE,
the SELECT statement in the FOR clause will traverse the index
once. It
will extract any matching records into a cursor. Subsequently
the UPDATE
will be issued against this cursor so it doesn't have to
traverse the index
again, like no. 1 and 3 above. Finally a condition is evaluated
and if it
returns True, the INSERT will be carried out.

Step 0a: Declare and initialize the variable (maybe this is not
significant?)
Step 1: Execute the SELECT statement and traverse the index to
fetch
matching records.
Step 2: The For loop will act as the condition here, since the
UPDATE and
INSERT are going to be mutually exclusive. Attempt an UPDATE if
any records
are available in the result set.
Step 3: Evaluate a condition on the value of the variable
Step 4: Attempt an INSERT if the condition evaluated to True.



I would personally favour solution no. 2 and/or no. 4, because
in the other
two solutions, we would be traversing the index twice, once to
establish the
existence and then to issue the UPDATE. Please do correct me if
I am wrong.
Also in no. 4 we could probably consider the creation of the
cursor as an
added operation. In all I would think no. 2 is the best for
updates as well
as inserts, because I think all solutions will fare equally well
for the
INSERT operation. Finally I am no expert on the intricacies of
a database
server, so please pull me up if I am wrong anywhere along this
discussion.
:-)



Thanks and Regards,

Anand S. Kashelkar
Mumbai



__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/