Numbering in SQL

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 :-)is this as 1 off or every time you INSERT a record?

--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.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 :-)|||Make the column a identity value, this way it is incremented with every
insert:

int IDENTITY (1, 1)|||lemes_m@.yahoo.com (lemes_m@.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> 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 ...|||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?|||>> 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, lemes_m@.yahoo.com , why do you want to destroy the relational
model? What is your business goal?|||Hi Lemes,

Check out http://blogs.msdn.com/sqlcat/archiv.../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

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.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 :-)