UPDATE		s
SET		s.ID = f.ID
FROM		{Your table name here} AS s
INNER JOIN	(
			SELECT		g.ID,
					w.Name
			FROM		(
						SELECT		d.ID,
								ROW_NUMBER() OVER (ORDER BY d.ID) AS recID
						FROM		(
									SELECT	ROW_NUMBER() OVER (ORDER BY ID) AS ID
									FROM	{Your table name here}
								) AS d
						LEFT JOIN	{Your table name here} AS s ON s.ID = d.ID
						WHERE		s.ID IS NULL
					) AS g
			INNER JOIN	(
						SELECT	ROW_NUMBER() OVER (ORDER BY Name) AS recID,
							Name
						FROM	{Your table name here}
						WHERE	ID IS NULL
					) AS w ON w.recID = g.recID
		) AS f ON f.Name = s.Name

quote:

---

Originally posted by frank.svs

Hi Pals,

Small Help Required.

Need to update all the values of the ID column by getting the MAX(id).
Something like need to generate sequnce numbers by getting the max(id) from the table.

Can we implement this using single update.

I have tried with the below update but i think NULL values are causing some problem.

We can doing this using single UPDATE instead of going to cursors or anyother looping statements.

Any help would be greatly appreciated.

Thanks!

Here is the script


create table temp_tbl
(id int,
name varchar(10)
)
insert into temp_tbl
select 1,'Ram'
union all
select null,'Sam'
union all
select null,'Sita'

UPDATE a
SET a.id =
(SELECT MAX(id)+1 FROM temp_tbl b
WHERE b.id = a.id )
FROM temp_tbl a

---