March 24, 2007 at 3:17 am
Hello,
I am having a table name "city" having fields sciid,cityname,srno.Field srno is "NULL".Now,I want to give numbering from 1 onwards to all rows having sciid=4.Can anyone help me in doing this?
Thanks,
Neetal
March 24, 2007 at 10:29 am
Try the following...
declare @sciid int
select @sciid = 0
while 1 = 1
begin
update city top (1) sciid = @sciid + 1 where sciid is null
if @@rowcount = 0 break
select @sciid + 1
end
MohammedU
Microsoft SQL Server MVP
March 26, 2007 at 10:54 am
select
sciid,
cityname,
rownum = when sciid = 4
then rank() over (partition sciid order by cityname)
else null
end
from city
order by cityname
March 26, 2007 at 11:48 am
I experimented and came up with this:
USE
tempdb;
IF EXISTS (SELECT * FROM sys.objects WHERE object_ID = OBJECT_ID(N'city') AND type IN (N'U'))
DROP TABLE city
CREATE TABLE city
(cityid INT IDENTITY(1,1),
sciid INT null);
INSERT
INTO city (sciid) values(55);
INSERT INTO CITY (sciid) values(NULL);
INSERT INTO CITY (sciid) values(55);
INSERT INTO CITY (sciid) values(NULL);
INSERT INTO CITY (sciid) values(NULL);
UPDATE
CITY SET sciid =c.rownum
FROM city JOIN
(SELECT c.cityid, ROW_NUMBER() OVER(PARTITION BY c.sciid ORDER BY c.cityid ) AS rownum
FROM city c
WHERE c.sciid IS NULL
) AS c
ON city.cityid = c.cityid
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply