Is there a better way of doing an INSERT or UPDATE

I have several different situations where I want to
Update the record if it exists
or
add a new record if it does not exist.
An example might be
----
-- Has this table already got a record for this entry...
select @.rowcnt=count(*) from mytable where myField = '123'
if ( @.rowcnt = 0 )
begin ... NO so create the record with an initial cnt of 1
insert into mytable (myField, cnt) values ( 'text123', 1 )
end
else
begin -- ... YES so increment the cnt
Update mytable set cnt=cnt+1 where myField = 'text123'
end
---
The select count(*) can take a long time on a large table.
Table Structure something like
--
id PK
myField varchar(32)
cnt int
--
Thanks
BillTry:
If Exists (select * from mytable where myField = '123')
update ...
else
insert ...
The Exists will perform much better than the Count because it will stop as
soon as it hits a match.
"Bill" <wje@.blueyonder.co.uk> wrote in message
news:33ff429ctg88ethir0ej3g4plhfbr19sqo@.
4ax.com...
>I have several different situations where I want to
> Update the record if it exists
> or
> add a new record if it does not exist.
> An example might be
> ----
> -- Has this table already got a record for this entry...
> select @.rowcnt=count(*) from mytable where myField = '123'
> if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> ---
> The select count(*) can take a long time on a large table.
> Table Structure something like
> --
> id PK
> myField varchar(32)
> cnt int
> --
> Thanks
> Bill|||The count(*) of your select will perform a scan on every record (either
indexed or otherwise) to find the number of instances. The higher the
rowcount, the longer it will take. Since it looks like (from your
example) that you are always looking to update exactly 1 row each time
use EXISTS instead. This will stop the reads once the record has been
located. Additionally, take a look at the indexes . . .:
if ( NOT EXISTS( select * from myTable where myField = '123') )
begin ... NO so create the record with an initial cnt of 1
insert into mytable (myField, cnt) values ( 'text123', 1 )
end
else
begin -- ... YES so increment the cnt
Update mytable set cnt=cnt+1 where myField = 'text123'
end
hope that helps . . .|||> The select count(*) can take a long time on a large table.
do you have a unique index on myField?|||Thanks very much.
Those replies were extremely useful.
Bill
On Thu, 20 Apr 2006 16:59:48 GMT, Bill <wje@.blueyonder.co.uk> wrote:

>I have several different situations where I want to
>Update the record if it exists
>or
>add a new record if it does not exist.
>An example might be
>----
>-- Has this table already got a record for this entry...
>select @.rowcnt=count(*) from mytable where myField = '123'
>if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
>else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
>---
>The select count(*) can take a long time on a large table.
>Table Structure something like
>--
>id PK
>myField varchar(32)
>cnt int
>--
>Thanks
>Bill|||As others have already pointed out, you can use exists instead of count(*),
but there's another problem with using the logic below in a concurrent
environment.
Here's what you need:
BEGIN TRAN
IF EXISTS (
SELECT *
FROM mytable WITH(UPDLOCK, HOLDLOCK)
WHERE myField = '123'
)
UPDATE...
ELSE
INSERT...
IF @.@.ERROR != 0 OR @.@.ROWCOUNT = 0
ROLLBACK
ELSE
COMMIT
What's most important here is the explicit transaction and WITH(UPDLOCK,
HOLDLOCK). Without these, you'll get inconsistent results in a concurrent
environment. Without the transaction, it's possible for another transaction
to delete the row between the time that the shared lock from the SELECT is
released and the time that the database engine tries to obtain an exclusive
lock for the UPDATE. Without both the transaction and HOLDLOCK, it's
possible for another transaction to insert a row where myField = '123'
between the time that EXISTS is evaluated and the time that the INSERT
starts executing. Without UPDLOCK, it's possible for two concurrent
instances to obtain and hold shared locks on the row where myField = '123'
such that neither can obtain an exclusive lock in order to do the UPDATE,
causing a deadlock. Now, that can happen only if the transaction isolation
level is stricter than READ COMMITTED, that is, REPEATABLE READ or
SERIALIZABLE. If the isolation level is READ COMMITTED (the default), then
other anomalies can occur, such as updates being lost, or primary key
constraint violations.
Thus, any time you issue a SELECT before an UPDATE or INSERT (even within an
EXISTS clause), you should wrap the whole thing in a transaction and
decorate the SELECT with the WITH(UPDLOCK, HOLDLOCK) clause. I prefer to
explicitly specify HOLDLOCK, even if the isolation level is SERIALIZABLE,
because that way if the isolation level is later changed (to improve
performance or scalability, for example), the above problems won't reappear.
"Bill" <wje@.blueyonder.co.uk> wrote in message
news:33ff429ctg88ethir0ej3g4plhfbr19sqo@.
4ax.com...
>I have several different situations where I want to
> Update the record if it exists
> or
> add a new record if it does not exist.
> An example might be
> ----
> -- Has this table already got a record for this entry...
> select @.rowcnt=count(*) from mytable where myField = '123'
> if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> ---
> The select count(*) can take a long time on a large table.
> Table Structure something like
> --
> id PK
> myField varchar(32)
> cnt int
> --
> Thanks
> Bill|||One comment: Using NOT EXISTS will also force a table or index scan. The
query processor has to scan all records to determine if one is missing.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"epperly" <epperlys@.gmail.com> wrote in message
news:1145553039.015639.244770@.i39g2000cwa.googlegroups.com...
> The count(*) of your select will perform a scan on every record (either
> indexed or otherwise) to find the number of instances. The higher the
> rowcount, the longer it will take. Since it looks like (from your
> example) that you are always looking to update exactly 1 row each time
> use EXISTS instead. This will stop the reads once the record has been
> located. Additionally, take a look at the indexes . . .:
> if ( NOT EXISTS( select * from myTable where myField = '123') )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> hope that helps . . .
>|||On Thu, 20 Apr 2006 22:01:01 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>One comment: Using NOT EXISTS will also force a table or index scan. The
>query processor has to scan all records to determine if one is missing.
First of all, if there is an index to support the subquery it will not
need to perform a scan.
Second, EXISTS performs exactly the same as NOT EXISTS. It has to, as
answering either question answers both questions.
Roy Harvey
Beacon Falls, CT