Incrementing a CHAR field 1

[leveetated]

Hello SQL experts,

I have a simple task, and despite my forum searches and Google searches I have not yet found a solution that "clicks" for me.

I have roughly 1300 rows, which is a subset of a table (items), where one field "itemNumber" (char (42)) needs to be incremented by one for each of the 1300 rows.

The current format looks like this - no particular order:

PTY0008.12.01
PTY0002.13.55
PTY0010.234.88
etc.

The format should end up looking like this:

1042.11.1
1042.11.2
1042.11.3
and so on, up to .1300.

I know this must be simple - I can't re-design the field to be an int field, but I'm at a loss & I've been looking at this too long!

Many thanks for all your kind help.

Best wishes.

 

[gmmastros]

What version of SQL Server are you using? Also, do you have a primary key column in this table?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[leveetated]

My mistake: SQL Server 2000, and yes, the items table has a primary key (itemId).

 

[bborissov]

DECLARE @Temp TABLE (id int IDENTITY(1,1), itemNumber CHAR(42))

INSERT INTO @Temp (itemNumber)
SELECT itemNumber
       FROM Items
--- ORDER BY ....  if you want some order


UPDATE Items
       SET ItemNumber = '1042.11.'+CAST(Tst.ID as varchar(20))
FROM Items
INNER JOIN @Temp Tst ON Items.ItemNumber = Tst.ItemNumber

NOT TESTED
Make a GOOD backup first.

Also if you ItemNumber field has NO unique values in it, you should change the insert to be:

INSERT INTO @Temp (itemNumber)
SELECT DISTINCT itemNumber
       FROM Items

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bborissov]

O BTW why you use CHAR(42)?
That is so much waste of space. Use varchar(42) instead.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[leveetated]

Many thanks for your helpful reply. Yes, I am able to insert the first row with the correct itemnumber as you suggested but the insert stops after just the one row...what should I be doing differently?

Thank you.

 

[leveetated]

I believe I figured it out, with thanks to bborissov's help! I failed to create a field in the temp table with an incremented value to append to the itemNumber field. Once that was created and plugged into the code above, the new itemNumbers now appear.

Many, many thanks for the help in re-focusing my efforts!!

Best wishes.