RE: SQL how to update field with seq # starting with next #

Try this:

create table teslib.table (seq dec(4,0), cust char(4), place char(15));

insert into teslib.table
values
(0000, '0101', 'walmart'),
(0000, '0102', 'target'),
(1234, '0104', 'McDonalds'),
(1235, '0105', 'Burger King');

Update teslib.table as A1 set A1.seq =
(select seqno from
(select case seq
when 0
then row_number() over() + (select max(seq) from
teslib.table)
else seq end as seqno,
cust, place from teslib.table) A2
where A2.cust = A1.cust);

Worked on v7.1 for me. I think there may be some issues if you want to
fill in gaps in the sequence numbering, but this should help.


-Tom Stieger
IT Manager
California Fine Wire




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Thursday, July 05, 2012 3:07 PM
To: Midrange Systems Technical Discussion
Subject: SQL how to update field with seq # starting with next #

I have file as follows:


Seq# CUST# name
------ -------- -------------
0000 0101 walmart
0000 0102 target

1234 0104 McDonalds
1235 0105 Burger King





I would like to run an SQL stmt to change all the seq zero fields to the
next higher #, so when finished, file would look like:


Seq# CUST# name
------ -------- -------------
1236 0101 walmart <-------------------- seq#
updated to next higher number
1237 0102 target <-------------------- seq#
updated to next higher number

1234 0104 McDonalds
1235 0105 Burger King



Can SQL do this easily?

Thanks!



______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.