Adding an identity column in an existing table in Sybase

[Former Member]

Hi All,

I have a table order with columns pin, orderPath, quantity. There are 5000 data in the table already. Now, I want to add an identy column, say OrderID, but with order by column pin.

Using the following query helps but not in the order as required.

ALTER TABLE order ADD OrderID numeric(6,0) identity

Here's what I got after executing the above statement:

Pin--OrderPath--Quantity--OrderID

22 kl/pod 100 1

11 xyz/pop 200 2

33 djh/dd 200 3

44 dj/po 300 4

But here's what I would like to see:

Pin--OrderPath--Quantity--OrderID

11 xyz/pop 200 1

22 kl/pod 100 2

33 djh/dd 200 3

44 dj/po 300 4

Please advice.

[MarkCulp]

What version and build number are you using?

You have not given us enough information. What do you mean by "not in the order as required".

Show us your table schema.

[Former Member]

The version of Sybase is 12, although not sure of the build number Executing the query "select @@version" provides me with the following information:

Adaptive Server Enterprise/12.0.0.8/P/EBF 12169 ESD3/NT (IX86)/OS 4.0/2076/32bit/OPT/Mon Dec 06 20:43:00 2004

I meant "not in the order as I actually needed"

Table Schema: create table Order( Pin numeric not null, OrderPath varchar null, Quantity int null, OrderID numeric(6,0) identity )

[VolkerBarth]

This forum deals with SQL Anywhere, not ASE. Therefore I would suggest to have a look at on one of Sybase's ASE newsgroups - see http://www.sybase.com/detail?id=1012843.

[MarkCulp]

Since the newsgroups have been shutdown, I suggest reading the last question in the FAQ and ask your question on the ASE SCN forum.

[VolkerBarth]

@Mark: Ah, I wasn't aware of the FAQ Update:)

[Breck_Carter]

To the down-voters: please STOP down-voting ASE questions! It's rude, and out of character for this forum. Thank you.

[former_SQLA_member1694874]

Agree, but no where near as rude as putting 'STOP' in full capitals, it suggests you think people are incapable of reading a sentence without you highlighting the important words for them, which also seems out of character for this forum (and you ?).

[VolkerBarth]

Agreed, though ASE in full capitals does seem to hurt some folks, too:)

Enough "nanny talk": Let's get back to our usual humourous and nice way of handling questions and answers...

[VolkerBarth]

If you really want to add a column that stores a particular order (instead of letting the engine order - and number - the results based on whatever criteria you need at that time), you can

• add a column with the appropriate data type and
• use an UPDATE statement to fill that for the existing rows, such as an self-joining UPDATE with an ROW_NUMBER() OLAP part, and
• would possibly need a trigger to insert/update that column for new/changed rows.

If the order should be "chronologically" and if skipping values are allowed, you could also use an easier way by adding a DEFAULT AUTOINCREMENT column which will fill the values based on the row's PK order, methinks (and if not, you could adapt that for the existing rows with an UPDATE, since DEFAULT AUTOINCREMENT can still be over-written explicitly).

---

If, on the other hand, you just need a "numbered" result set (which I would highly recommend), you can easily do this with ROW_NUMBER(), such as the - untested -

SELECT Pin, OrderPath, Quantity,
   ROW_NUMBER() OVER (ORDER BY Pin) as OrderID
FROM MyTable
ORDER BY OrderID

Apparently, ORDER BY Pin would do, as well.