February 27, 2008 at 11:51 am
select * from dbo.test
id name
----------- ----------
1 abc
2 def
3 abcd
4 def
5 abcds
6 def
7 def
8 ajk
(8 row(s) affected)
so here id is the idenity property (1,1).... when i delete the row s like
delete dbo.test
where name='def'
select * from dbo.test
id name
----------- ----------
1 abc
3 abcd
5 abcds
8 ajk
(4 row(s) affected)
so here the id display as 1,3,5,8 but i jus want it to be 1,2,3,4, is that possible plz let me know ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 27, 2008 at 12:25 pm
There is not a simple way to do what you want. You can remove identity from the column set the values to null and put the identity property back, I think. You could also do select into a new table, delete the old table, and rename the new table to the old table name.
For what you are doing identity is not the way to go for the Id.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2008 at 12:30 pm
oh k thkz jack so its not that easy to do that ...k thkz a lot
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 27, 2008 at 1:05 pm
This should help you, or at least point you in the right direction.
declare @t table(
id int,
name varchar(10)
)
insert into @t
select 1, 'abc' union all
select 2, 'def' union all
select 3, 'abcd' union all
select 4, 'def' union all
select 5, 'abcds' union all
select 6, 'def' union all
select 7, 'def' union all
select 8, 'ajk'
delete
from @t
where id in(
select a.id
from @t a
inner join
(
select name, MIN(ID) as [ID]
from @t
group by name
having count(*) > 1
) as b
on a.name = b.name AND a.ID <> b.ID
)
select *
from @t
February 27, 2008 at 1:23 pm
I TRIED THE CODE U GAVE ME BUT STILL I GET THE SAME OUTPUT AS BEFORE WITH ID AS 1,3,5,8 ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 27, 2008 at 1:31 pm
kurraraghu,
The output is not the same. The output for the query is 1,2,3,5,8. I am rkeeping the smallest id of the duplicate data, in this case id 2. Thus 4,6,7 are deleted.
February 27, 2008 at 1:37 pm
I GOT SOME INFO FROM THAT BUT WHAT I WANT IS WHEN THE ROWS 54,6,7 GETS DEKLETED I JUS WANT THE REST OF THE ROWS TO BE IN SEQUENTIAL ORDER I MEAN WHEN U JUS QUERY THE TABLE IT SHULD RETURN
SELECT * FROM @T
ID 1,2,3,4
NAME ABC,DEF,ABCD,AHSD
THE ID SHOULD BE IN SEQUNETIAL ORDER AS THE ROW 4 IS DELETD IT SHOULD ME 1,2,5, THAT WAY....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 27, 2008 at 1:46 pm
What are you using this for? If you're just using it for some kind of display purposes - skip messing with the actual column, and use a calculated ROW_NUMBER().
Select ROW_NUMBER() over (order by ID) as RN,
Name
from mytable
If you're using this table as a lookup/reference table, you really don't want to just go in and start changing values, since that will destroy any referential integrity you might have going....
Identity fields are built the way they are for a solid reason. Why do you need it as an unbroken sequence?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 1:54 pm
GOT IT THKZ BRO...BUT I CANT CHANGE THE IDENTITY VALUE RIGHT IT MAKES A WHOLE MESS IN THE COLUMN ...ANYWYA I JUS NEED THIS THKZ BRO....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 27, 2008 at 2:04 pm
Well, if you need the values in a sequential order then you have to use the row_number() function, as Matt suggested.
It does not make sense that you want to jeopardize data integrity to have sequential ordering. Is this going to be displayed in a data grid? What happens when the user click the row number thinking they are modifing a particular id and they are not; or how would the detail screen relate back to this table?
The code to make it sequential:
declare @t table(
id int,
name varchar(10)
)
insert into @t
select 1, 'abc' union all
select 2, 'def' union all
select 3, 'abcd' union all
select 4, 'def' union all
select 5, 'abcds' union all
select 6, 'def' union all
select 7, 'def' union all
select 8, 'ajk'
delete
from @t
where id in(
select a.id
from @t a
inner join
(
select name, MIN(ID) as [ID]
from @t
group by name
having count(*) > 1
) as b
on a.name = b.name AND a.ID <> b.ID
)
select row_number() over (order by id) as [ID],
Name
from @t
ORDER BY Id
February 27, 2008 at 2:07 pm
Just about the only way to "resequence" an identity table like what you wish is to drop the identity column, and add a new identity column. And voila - new sequence. And - like we mentioned earlier - a mess if anything used to be referring to the ID's you just replaced.
Just remember though - there is NO GUARANTEE that identity fields will not have gaps. It's very easy to introduce gaps, and they may just happen.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply