Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

update vs delete insert

3 posts in General Discussion (old) Last posting was on 2010-04-16 18:18:13.0Z
Ontsnapt Posted on 2010-04-16 09:34:21.0Z
From: Ontsnapt <ontsnapt@hotmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; nl; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
Subject: update vs delete insert
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bc82f1d@forums-1-dub>
Date: 16 Apr 2010 02:34:21 -0700
X-Trace: forums-1-dub 1271410461 10.22.241.152 (16 Apr 2010 02:34:21 -0700)
X-Original-Trace: 16 Apr 2010 02:34:21 -0700, vip152.sybase.com
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:347
Article PK: 841549

Working on a ASA 10 database.

In a stored procedure is a statement like.

delete t1 where x > .. and x < ...;
insert into t1 (c1, c2) select (c1, c2) from v_1 where x > .. and x < ..;

I would like to replace the delete and insert with a update statement
because I think it will increase performance (???).

Is that possible? If so, what the best way.

Thans
Eric


Chris Keating [Sybase] Posted on 2010-04-16 18:18:13.0Z
From: "Chris Keating [Sybase]" <keating_nospam@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
Subject: Re: update vs delete insert
References: <4bc82f1d@forums-1-dub>
In-Reply-To: <4bc82f1d@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bc8a9e5@forums-1-dub>
Date: 16 Apr 2010 11:18:13 -0700
X-Trace: forums-1-dub 1271441893 10.22.241.152 (16 Apr 2010 11:18:13 -0700)
X-Original-Trace: 16 Apr 2010 11:18:13 -0700, vip152.sybase.com
Lines: 21
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:349
Article PK: 841547

And in SA11, you can use MERGE.

-chris

On 4/16/2010 5:34 AM, Ontsnapt wrote:
> Working on a ASA 10 database.
>
> In a stored procedure is a statement like.
>
> delete t1 where x > .. and x < ...;
> insert into t1 (c1, c2) select (c1, c2) from v_1 where x > .. and x < ..;
>
> I would like to replace the delete and insert with a update statement
> because I think it will increase performance (???).
>
> Is that possible? If so, what the best way.
>
> Thans
> Eric


Tyson Lewis Posted on 2010-04-16 16:20:46.0Z
From: Tyson Lewis <first.name.last.name@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
Subject: Re: update vs delete insert
References: <4bc82f1d@forums-1-dub>
In-Reply-To: <4bc82f1d@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bc88e5e$1@forums-1-dub>
Date: 16 Apr 2010 09:20:46 -0700
X-Trace: forums-1-dub 1271434846 10.22.241.152 (16 Apr 2010 09:20:46 -0700)
X-Original-Trace: 16 Apr 2010 09:20:46 -0700, vip152.sybase.com
Lines: 32
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:348
Article PK: 866770

There is another statement you could use to achieve this. But this is
assuming that the INSERT statement you are using replaces all the rows
from the DELETE statement.

There is an option for the insert statement you could use like:

INSERT INTO t1(c1, c2) ON EXISTING UPDATE SELECT c1, c2 WHERE X BETWEEN
(low + 1) AND (high - 1)

Note here I replaced x > low and x < high with x BETWEEN (low + 1) AND
(high - 1). Both should achieve the same functionality, but the BETWEEN
allows one less predicate to be written.

On 4/16/2010 3:34 AM, Ontsnapt wrote:
> Working on a ASA 10 database.
>
> In a stored procedure is a statement like.
>
> delete t1 where x > .. and x < ...;
> insert into t1 (c1, c2) select (c1, c2) from v_1 where x > .. and x < ..;
>
> I would like to replace the delete and insert with a update statement
> because I think it will increase performance (???).
>
> Is that possible? If so, what the best way.
>
> Thans
> Eric

--
Tyson Lewis, Sybase iAnywhere