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.

Help with updating columns with sequential numbers

3 posts in General Discussion Last posting was on 2012-07-11 02:30:55.0Z
Mark Pare Posted on 2012-07-10 18:29:44.0Z
Sender: 647d.4ffc731a.1804289383@sybase.com
From: Mark Pare
Newsgroups: sybase.public.ase.general
Subject: Help with updating columns with sequential numbers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffc7498.64b6.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 10 Jul 2012 11:29:44 -0700
X-Trace: forums-1-dub 1341944984 172.20.134.41 (10 Jul 2012 11:29:44 -0700)
X-Original-Trace: 10 Jul 2012 11:29:44 -0700, 172.20.134.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31235
Article PK: 74122

Hi, I have a table that I have to fill with customer
numbers. Let's say I am about to insert 3 records, I have:

Cust# Action FN LN
null I Bob Smith
null I David Jones
null I Jane Doe

and my customer number field exists in another table and is
currently at 5614303.

Rather than use a cursor for update on the customer table,
is there a way to do it in a single SQL statement so that
the 3 records would get 5614303, 5614304 and 5614305 for
customer numbers?

I know Oracle has a sequence which would fill in perfectly
here, wondering if Sybase has something similar?


Let me know. Thanks

Adaptive Server Enterprise/12.5.4/EBF 18267 ESD#10
ONE-OFF/P/x86_64/Enterprise
Linux/ase1254/2160/64-bit/OPT/Mon Sep 13 00:37:43 2010


Bret Halford Posted on 2012-07-10 20:29:07.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Help with updating columns with sequential numbers
References: <4ffc7498.64b6.1681692777@sybase.com>
In-Reply-To: <4ffc7498.64b6.1681692777@sybase.com>
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: <4ffc9093$1@forums-1-dub>
Date: 10 Jul 2012 13:29:07 -0700
X-Trace: forums-1-dub 1341952147 172.20.134.152 (10 Jul 2012 13:29:07 -0700)
X-Original-Trace: 10 Jul 2012 13:29:07 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31236
Article PK: 74125

The typical solution would be to have
the customer number field in the customer table
be an identity column. If it is important
that the customer numbers never have gaps,
then the identity_gap property for the table could
be set to 1.

On 7/10/2012 12:29 PM, Mark Pare wrote:
> Hi, I have a table that I have to fill with customer
> numbers. Let's say I am about to insert 3 records, I have:
>
> Cust# Action FN LN
> null I Bob Smith
> null I David Jones
> null I Jane Doe
>
> and my customer number field exists in another table and is
> currently at 5614303.
>
> Rather than use a cursor for update on the customer table,
> is there a way to do it in a single SQL statement so that
> the 3 records would get 5614303, 5614304 and 5614305 for
> customer numbers?
>
> I know Oracle has a sequence which would fill in perfectly
> here, wondering if Sybase has something similar?
>
>
> Let me know. Thanks
>
> Adaptive Server Enterprise/12.5.4/EBF 18267 ESD#10
> ONE-OFF/P/x86_64/Enterprise
> Linux/ase1254/2160/64-bit/OPT/Mon Sep 13 00:37:43 2010
>


hy Posted on 2012-07-11 02:30:55.0Z
From: "hy" <nospam_harrylhy@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4ffc7498.64b6.1681692777@sybase.com>
In-Reply-To: <4ffc7498.64b6.1681692777@sybase.com>
Subject: Re: Help with updating columns with sequential numbers
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ffce55f@forums-1-dub>
Date: 10 Jul 2012 19:30:55 -0700
X-Trace: forums-1-dub 1341973855 172.20.134.152 (10 Jul 2012 19:30:55 -0700)
X-Original-Trace: 10 Jul 2012 19:30:55 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31237
Article PK: 74126

You can perform the update sql in trigger or stored procedure like following;


declare @i int

select @i = (select cust_no from <your cust no. key table>)

select @i = @i + 1 <--- if your key table hold the last key, you should add this syntax for increment of key value first


begin tran

-- following sql update your column value first, then add 1 to @i, place '@i = @i + 1' before or after 'cust_no = @i' is
equivalent

update <your cust record table>
set cust_no = @i, @i = @i + 1
where ... (e.g., cust_no = null, action = 'I' etc.)

commit tran



Hope this help :)

"Mark Pare" 礎b繞l瞼籀簣i繞K瞻繙簧e瞼D礎簧 4ffc7498.64b6.1681692777@sybase.com 瞻瞻翹繞翹g...

> Hi, I have a table that I have to fill with customer
> numbers. Let's say I am about to insert 3 records, I have:
>
> Cust# Action FN LN
> null I Bob Smith
> null I David Jones
> null I Jane Doe
>
> and my customer number field exists in another table and is
> currently at 5614303.
>
> Rather than use a cursor for update on the customer table,
> is there a way to do it in a single SQL statement so that
> the 3 records would get 5614303, 5614304 and 5614305 for
> customer numbers?
>
> I know Oracle has a sequence which would fill in perfectly
> here, wondering if Sybase has something similar?
>
>
> Let me know. Thanks
>
> Adaptive Server Enterprise/12.5.4/EBF 18267 ESD#10
> ONE-OFF/P/x86_64/Enterprise
> Linux/ase1254/2160/64-bit/OPT/Mon Sep 13 00:37:43 2010