February 13, 2009 at 1:59 am
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 in advance.
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
February 13, 2009 at 2:16 am
Do you want to generate the ID numbers from 1 to .... in order or you have other values that you should find the MAX value then the new record will add incrementing by 1
this is one of the solution if you want to generate the order number from 1 to how many records you have
WITH UPI
AS
(SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS NEW_ID, ID, NAME FROM TEMP_TBL)
UPDATE UPI
SET ID = NEW_ID
Hope it helps!
:hehe:
February 13, 2009 at 2:27 am
Thank You.
Can we do the same thing using update and corelated sub-query?
February 13, 2009 at 5:53 am
You can, but why not use the CTE?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply