Another SQL Question

[Panda On Smack]

Hola

I have a table in my database containg about 30,000 people, some of these people are members and ive been told i need to give each of them a membership number starting at 10001 or something similar.

Can anybody think of a easy way in SQL to select these members and insert a membership number on the fly to their row? Adding 1 to the number each time i suppose

Ta

 

[KevinUK]

Can you give us some sample data? Just of 1 row. It will be easy enough to do.

 

[anattic]

I haven't used SQL much recently, so apologies if this is gibberish. Dunno which DB you're using, so I guessed at MySQL:

# Assuming you've a table called 'foo', and you want to add a column 'memberid':
# Note: MySQL can only support 1 auto-incrementing column, which must be defined as a key.

set insert_id=10001;   # assuming that's where you want the numbering to start
alter table foo add memberid INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(memberid);

select * from foo;   # should now have a memberid attached

The alter command is explained in detail here: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Hope it helps.

 

[SheepCow]

That will give non-members IDs too, if that's what you want.

 

[anattic]

True -- I missed the "some of these people..." bit. Note to self -- RTFQ.

 

[Moo]

just add a where match which matches only non-members for ur table.

 

[SheepCow]

just add a where match which matches only non-members for ur table.

You wouldn't be able to do that for what anattic said above.

 

[SheepCow]

You wouldn't be able to do that for what anattic said above.

You could do this with a very short PHP script very easily, you could:

1. Add a memberid column (not auto inc, probably want it indexed)
2. Select all the members
3. Start with a counter at 10001 and update the memberid column on all of the members

When a new member is added you'd need to select the MAX memberid, increment, and save with that id.

A "nicer" way would be anattic's way, but if you don't want to give non-members the ID number you're left with fewer options.

 

[Panda On Smack]

Cheers guys, didnt see the replies

The ID is for members only so i would need to select them first, i thought of creating an asp file which just selects members and then loops through them updating their row with an ID number which is 1 greater than the last. Dunno how much resources this would consume though and how long it would take.

its MS SQL 2000

Ta

 

[SheepCow]

Updating 30,000 records ... 30 seconds? 5 seconds? Maybe 10 minutes if you're running your SQL server on an Intel 8080

 

[Shovel]

The ID is for members only so i would need to select them first, i thought of creating an asp file which just selects members and then loops through them updating their row with an ID number which is 1 greater than the last. Dunno how much resources this would consume though and how long it would take.

I'm not sure of time, but the estimates SheepCow makes seem reasonable. I did a lot of work with a grotesquely large SQL Server database last year and the majority of what we did (which sounds like a similar scale to your process) was done in less than 10 seconds.

Only other thing: I don't see that you need to use ASP for anything? T-SQL's cursor syntax should be able to take care of looping through your selected members and adding a membership number. I can do an example if you'd like, but won't post one here since 1) I'm pretty sure you've been working for SQL Server for a while so there's every chance you already know what to do and 2) I always get cursors wrong and need to tweak my code a dozen times before it will work