Hi,

I have a table with data in it. But one of the columns is NULL right now, How can I insert a sequential numbering into this column without altering the table?

Existing table:

NUMBER NAME LASTNAME
NULL Jhon Allen
NULL Peter Pan
NULL Peter Parker

Required Data:

NUMBER NAME LASTNAME
0254 Jhon Allen
0255 Peter Pan
0256 Peter Parker



=======================
Strange and crazy, but everything is possible

__________________
=======================
Strange and crazy, but everything is possible

I was thinking something like this:

declare @seq int
SET @seq =0254
UPDATE FTX_TIEDSS set number=@seq+1

But it dosen't work it just update the first record, inserting number 0255

:)

=======================
Strange and crazy, but everything is possible

DECLARE @Seq INT
    SET @Seq = 0254
UPDATE FTX_TIEDSS
   SET @Seq = Number = @Seq+1

It would, however, be much better if the Number column were an IDENITY column...

--Jeff Moden

WoW, That was a fast response..

Thanks Jeff.. :)

=======================
Strange and crazy, but everything is possible

Heh... like you said, "Strange and crazy, but everything is possible"... especially if you just happen to know the answer :D

Thanks for the feedback...

--Jeff Moden

You sure that works??

In what order would the rows be updated, i.e. which rows would get which values?

Jeff Mason
Custom Apps, Inc.
[email protected]

Jeff,

You're right... Pretty sure it would be in whatever order the clustered index is in... if he has one... otherwise, unpredictable. Hope he has a clustered index on the names or we'll have to figure something else out :D

Ricespn,

Yep, Jeff is right... I flat out forget about ordering (sorting) in the order you want. If you don't have a clustered key on the names, we'll need to figure something else out. Write back with what you have, please.




--Jeff Moden

It's worse than that. You cannot depend on the order even if there is an index, clustered or otherwise. Ordering implies a cursor, and unlike SELECT, the UPDATE statement has no mechanism to construct one (i.e. no ORDER BY clause). The query processor is going to update all the rows in whatever order it wants to and you connect change that. Indeed, the actual effect of an UPDATE statement is that all of the rows in the set of rows defined by the WHERE clause are updated all at once, that is, simultaneously. There's no WHERE clause, so that means all the rows in the table are updated all at once.

Moreover, while the construct SET @Seq = Number = @Seq+1 is allowed syntactically, when I tried it I was surprised to find that although the @@rowcount indicated that all rows were processed, nothing was changed. I suspect that the original NULL values for the Number column propagated throughout the expression somehow, probably an artifact of the way all rows of the UPDATE statement are processed simultaneously.

I think the only way to do this reliably is to use a cursor, once the OP decides exactly which rows are to get which sequential values. That or use an identity column, either in this table or a temporary one.

Jeff Mason
Custom Apps, Inc.
[email protected]

Hi Guys,

Actually the order or the number assigned to the rows don't matter much, I just wanted to insert a number that is different on each row regardless of sequence... This is a test table and the number is going to be generated by a program, but in order to test my code I needed to assign values for all the rows without modifying the table.

Jeff Moden,

I try your query yesterday night and I find out that my column is data type VARCHAR and I'm using an INT value and I'm having a hard time trying to convert it. So I don't know if it really works or not...


=======================
Strange and crazy, but everything is possible

Jeff...
Always a pleasure to meet the proverbial kindred spirit. Thank you for the feedback and the thoughtful reply.

I think, however, that you may have made a mistake somewhere in your testing. The following not only runs fine, even with the NULLs in the Number column, but the final result is ordered by name as are the sequence numbers as was intended...

----================================================== ===================
-- Set up the test...
--================================================== ===================
--===== Supress the auto-display of rowcounts for appearance and speed
    SET NOCOUNT ON

--===== If the test table exists, drop it
     IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
        DROP TABLE #MyHead

--===== Create the test table
 CREATE TABLE #MyHead
        (
        Number VARCHAR(10),
        LastName VARCHAR(25) NOT NULL,
        FirstName VARCHAR(25) NOT NULL
        )

--===== Add a clustered primary key
  ALTER TABLE #MyHead
    ADD PRIMARY KEY CLUSTERED (LastName,FirstName)

--===== Add non ordered data to the test table
 INSERT INTO #MyHead
        (LastName,FirstName)
 SELECT 'Davolio','Nancy' UNION ALL
 SELECT 'Fuller','Andrew' UNION ALL
 SELECT 'Leverling','Janet' UNION ALL
 SELECT 'Peacock','Margaret' UNION ALL
 SELECT 'Callahan','Laura' UNION ALL
 SELECT 'Dodsworth','Anne'

--===== Add some more non ordered data
 INSERT INTO #MyHead
        (LastName,FirstName)
 SELECT 'Suyama','Michael' UNION ALL
 SELECT 'Buchanan','Steven' UNION ALL
 SELECT 'King','Robert'

--================================================== ===================
-- Run the test code
--================================================== ===================
--===== Declare and preset the sequence variable
DECLARE @Seq VARCHAR(10)
    SET @Seq = 250

--===== Do the proprietary update that everyone says won't work
 UPDATE #MyHead
    SET @Seq = Number = @Seq + 1

--===== The proof is in the pudding...
 SELECT * FROM #MyHead


Ricespn...

The code above solves your problem even when the Number column is character based. You will, of course, need to change the table name and the column names to match your situation. Also, if the table does not have a clustered primary key on the names, as in the code above, then you will need to add one as in the code above. If you already have a clustered PK or clustered index on columns other than the last name and first name, then you will need to change it.

--Jeff Moden