Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 30th, 2006, 07:00 PM
Authorized User
Points: 245, Level: 4
Points: 245, Level: 4 Points: 245, Level: 4 Points: 245, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: Houston, TX, USA.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ricespn
Default Sequential Numbers

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
Reply With Quote
  #2 (permalink)  
Old October 30th, 2006, 07:03 PM
Authorized User
Points: 245, Level: 4
Points: 245, Level: 4 Points: 245, Level: 4 Points: 245, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: Houston, TX, USA.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ricespn
Default

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
Reply With Quote
  #3 (permalink)  
Old October 30th, 2006, 07:21 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old October 30th, 2006, 07:28 PM
Authorized User
Points: 245, Level: 4
Points: 245, Level: 4 Points: 245, Level: 4 Points: 245, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: Houston, TX, USA.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ricespn
Default

WoW, That was a fast response..

Thanks Jeff.. :)

=======================
Strange and crazy, but everything is possible
Reply With Quote
  #5 (permalink)  
Old October 30th, 2006, 08:14 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old October 30th, 2006, 08:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
je.mason@comcast.net
Reply With Quote
  #7 (permalink)  
Old October 31st, 2006, 01:04 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old October 31st, 2006, 08:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
je.mason@comcast.net
Reply With Quote
  #9 (permalink)  
Old October 31st, 2006, 10:20 AM
Authorized User
Points: 245, Level: 4
Points: 245, Level: 4 Points: 245, Level: 4 Points: 245, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: Houston, TX, USA.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ricespn
Default

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
Reply With Quote
  #10 (permalink)  
Old October 31st, 2006, 10:27 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
quote:Originally posted by Jeff Mason
 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.
je.mason@comcast.net
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
sequential transformations alexan XSLT 0 July 24th, 2008 11:59 PM
How to have Sequential numbers in a recordset? hman Beginning PHP 1 June 21st, 2008 07:23 AM
Sequential numbering help gear1 Access 1 April 9th, 2007 06:31 AM
Sequential Numbers ricespn Access 5 February 17th, 2007 02:54 AM



All times are GMT -4. The time now is 08:27 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.