How do i update distinct column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do i update distinct column

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2009-02-24 : 17:37:15

Hello guys,
how do i update a distinct column, without using a temp table ?

thanks
MM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 17:41:03

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-25 : 03:39:50

see this sample data
declare @t table (id int , ename varchar(32))

insert into @t select 12, 'bklr'
insert into @t select 6, 'raju'
insert into @t select 2, 'hari'
insert into @t select 4, 'nag'
insert into @t select 1, 'chiru'
insert into @t select 12, 'bklr'
select * from @t
update t
set ename = 'hai'
from
(select row_number() over (partition by id order by id) as seq , * from @t)t
where t.seq = 1 and id = 12
select * from @t

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-25 : 03:45:26

Also Try This,

declare @t table (id int , ename varchar(32))

insert into @t select 12, 'bklr'
insert into @t select 6, 'raju'
insert into @t select 2, 'hari'
insert into @t select 4, 'nag'
insert into @t select 1, 'chiru'
insert into @t select 12, 'bklr'

update top (1) @t
set ename = 'nagesh'
where id = 12

select * from @t

missMac
Posting Yak Master

124 Posts

Posted - 2009-02-26 : 08:42:20

Works well. Thanks a lot guys. brilliant

But cant it be done dynamically, when you dont know when the id = 12 ???

As we have a lot of rows

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 05:02:27

quote:
Originally posted by missMac

Works well. Thanks a lot guys. brilliant

But cant it be done dynamically, when you dont know when the id = 12 ???

As we have a lot of rows

Using bklr's approach


declare @t table (id int , ename varchar(32))

insert into @t select 12, 'bklr'
insert into @t select 6, 'raju'
insert into @t select 2, 'hari'
insert into @t select 2, 'hari'
insert into @t select 4, 'nag'
insert into @t select 1, 'chiru'
insert into @t select 12, 'bklr'
select * from @t

update t 
set ename = 'hai'
from 
(select row_number() over (partition by id order by id) as seq , * from @t)t 
where t.seq = 1 and exists(select id from @t where id=t.id group by id having count(*)>1)
select * from @t

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 10:23:37

quote:
Originally posted by madhivanan

quote:
Originally posted by missMac

Works well. Thanks a lot guys. brilliant

But cant it be done dynamically, when you dont know when the id = 12 ???

As we have a lot of rows

Using bklr's approach

declare @t table (id int , ename varchar(32))

insert into @t select 12, 'bklr'
insert into @t select 6, 'raju'
insert into @t select 2, 'hari'
insert into @t select 2, 'hari'
insert into @t select 4, 'nag'
insert into @t select 1, 'chiru'
insert into @t select 12, 'bklr'
select * from @t

update t
set ename = 'hai'
from
(select row_number() over (partition by id order by id) as seq , * from @t)t
where t.seq = 1 and exists(select id from @t where id=t.id group by id having count(*)>1)
select * from @t

Madhivanan

Failing to plan is Planning to fail

slight modification


update t 
set ename = 'hai'
from 
(select row_number() over (partition by id order by id) as seq , 
count(1) over (partition by id) as occurance,
* from @t)t 
where t.seq = 1 
and t.occurance>1

missMac
Posting Yak Master

124 Posts

Posted - 2009-03-05 : 17:09:04

thanks guys, just saw this late.

You guys are wizards. LOL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:42:35

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources