By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Numbering in SQL

P: n/a
I have table with 10-20 rows with field P6 which is empty. I want to
update numbers to P6 starting 1 and increasing by 1. I suppose it is
done by triggers but I don't know how to do that. Help :-)

May 19 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
is this as 1 off or every time you INSERT a record?

--
----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<le*****@yahoo.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
I have table with 10-20 rows with field P6 which is empty. I want to
update numbers to P6 starting 1 and increasing by 1. I suppose it is
done by triggers but I don't know how to do that. Help :-)

May 19 '06 #2

P: n/a
das
Make the column a identity value, this way it is incremented with every
insert:

int IDENTITY (1, 1)

May 19 '06 #3

P: n/a
le*****@yahoo.com (le*****@yahoo.com) writes:
I have table with 10-20 rows with field P6 which is empty. I want to
update numbers to P6 starting 1 and increasing by 1. I suppose it is
done by triggers but I don't know how to do that. Help :-)


Without further knowledge about the table it is difficult to give
advice. And if the rest of the data is not unique, it's getting sort
of ugly.

For a one-off you could do:

DECLARE @i int
SELECT @i = 1
-- SET ROWCOUNT 1 Use this on SQL 2000.
WHILE EXISTS (SELECT * FROM tbl WHERE P6 IS NULL)
BEGIN
UPDATE /* TOP(1) */ tbl -- Remove comment for SQL 2005.
SET P6 = @i

SELECT @i = @i + 1
END
-- SET ROWCOUNT 0 again, for SQL 2000.

But I would not like to see this code in a trigger.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 19 '06 #4

P: n/a
>> have table with 10-20 rows with field [sic] P6 which is empty [sic]. <<

You have NEVER written SQL before, have you? Columsn are not fields
and we have NULLs, not the empty spreadsheet cells you assume. TOTALLY
WRONG MINDSET!
I want to update numbers to P6 starting 1 and increasing by 1. <<


That is a SEQUENTAL MAGNETIC TAPE FILE and you are tryignto write
1950's code in SQL! It has northing whatsoever to do with RDBMS.
Tables have no ordering by definition. This is soooooo wrong ...

May 19 '06 #5

P: n/a
Geeesh!!! You can insert a set at a time, so adding one to a previous
value makes no sense. Doesn't anyone go to RDBMS classes any more?

May 19 '06 #6

P: n/a
>> Make the column a identity value, this way it is incremented with every insert: <<

Always go for the proprietary and most non-relational kludge? LET'S
FINBD OUT WHAT HE IS REALLY TRYIGN TO DO BEFORE WE POST ANYTHING ELSE.
Okay, le*****@yahoo.com , why do you want to destroy the relational
model? What is your business goal?

May 19 '06 #7

P: n/a
Hi Lemes,

Check out http://blogs.msdn.com/sqlcat/archive...10/572848.aspx it has
good examples of how to do this.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<le*****@yahoo.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
I have table with 10-20 rows with field P6 which is empty. I want to
update numbers to P6 starting 1 and increasing by 1. I suppose it is
done by triggers but I don't know how to do that. Help :-)

May 20 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.