|
|
Posted by Erland Sommarskog on 05/20/06 00:46
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|