Update Query to Insert Incremental Numbers

Question

Post reply

Update Query to Insert Incremental Numbers

garybronson

SSC Enthusiast

Points: 160
More actions
November 10, 2007 at 10:44 am

#376963

I have a customers table that contains a number that is basically know as the customer pass number. The pass number is supposed to be unique but since some are manually entered through a form I have noticed duplicates and some that are just blank or NULL. I can make corrections to the form to require this field and I can also stop duplicates going forward but I really need to do a mass update in the table to correct at least the ones that are NULL. I'm using SQL Server 2005 and I think I need to create a stored procedure to do this. What I want to do is have a starting number like 120000 and for each NULL pass number I find I want to assign the next incremental number like 120001 and so on until all pass numbers are inserted. I know I can write a VB program to do it but I wanted to learn more about SQL. Can someone explain how to do this.
here is pseudo code:
newnumber = 120000
For Each Customer in customers
If passnumber = NULL Then
Customer.passnumber = [newnumber+1]
End If
Next Customer
GaryB

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Add an IDENTITY column to the table with the correct seed...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

garybronson SSC Enthusiast Points: 160 More actions · Answer 2

Correct me if I'm wrong but your suggestion would prevent the problem from happening in the future which I don't need right now. I need to update all of the current records make them correct first.

Thanks

GaryB

Ian Yates SSCoach Points: 19738 More actions · Answer 3

How about something like

update customers

set passNumber = blankCustomers.rowNum + 100000 --big first starting number

from customers

inner join (

select customerID, row_number() OVER (ORDER BY customerID) as rowNum

from customers

where passNumber is null

) blankCustomers

on customers.customerID = blankCustomers.customerID

I've only just bothered to learn some of the new syntax with OVER, etc (after reading a VERY long post here a few weeks back debating the merits of it, etc - our customers still run SQL 2k anyway)... This can be done without it with a few more lines of SQL. I think the syntax is correct - I haven't attempted to execute it.

If you also need to get rid of those duplicates you could do similar code to add a suffix A, B, C, D etc to the end of their passnumber if passnumber was a varchar field rather than an int field. You'd partition the row_number() function above by passNumber and then convert the rowNum int result into a char... And obviously you'd change the where clause to not look for NULLs but instead look for duplicates.

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 4

UPDATE customers

SET passnumber = passnumber + 12000

WHERE passnumber is null

In Theory, theory and practice are the same...In practice, they are not.

garybronson SSC Enthusiast Points: 160 More actions · Answer 5

update customersset passNumber = blankCustomers.rowNum + 100000 --big first starting numberfrom customers inner join ( select customerID, row_number() OVER (ORDER BY customerID) as rowNum from customers where passNumber is null ) blankCustomers on customers.customerID = blankCustomers.customerID

This really worked great.

Thanks a lot.

GaryB

garybronson SSC Enthusiast Points: 160 More actions · Answer 6

Sorin Petcu (11/12/2007)
UPDATE customers
SET passnumber = passnumber + 12000
WHERE passnumber is null

I think all this would have done is set them all to 12000.

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 7

yeah, you right. I have a query in my scripts which make the same thing of what you want. I've copied it partially.

Where clause was wrong (id of row is something). Anyway, no bother with me. I saw that you solved the problem.

In Theory, theory and practice are the same...In practice, they are not.

Smivvy Valued Member Points: 53 More actions · Answer 8

Will this snippet work with the Nopcart script to generate a customer number in the actual order email sent to the supplier ?