Update Multiple row from same table

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)

Update Multiple row from same table

Author

Topic

munami
Starting Member

2 Posts

Posted - 2010-11-08 : 05:21:06

Hi,

I have table like this (ID,NAME,PASSWORD)

1 max 123 OLD
2 rex 444 NEW
3 max 678 OLD
4 max 900 OLD
5 meyson 333 NEW
6 max 675 NEW
7 tania 1200 OLD
8 tania 666 OLD

I need to update all Passwords with those rows with same Name so the result should be like this. Ex: Password for all rows with Name max changed to 123.

1 max 123 OLD
2 rex 444 NEW
3 max 123 OLD
4 max 123 OLD
5 meyson 333 NEW
6 max 123 NEW
7 tania 1200 OLD
8 tania 1200 OLD

So how can i achieve this with one update command.

Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 05:27:11

Can I ask why there are so much duplicates in a User table?

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 05:33:41

[code]
declare @t table(id int identity,nm varchar(30),pswd varchar(30))
insert @t
select 'max','123' union all
select 'max','678' union all
select 'max','900' union all
select 'tania','1200' union all
select 'tania','666'

select * from @t

update t1 set t1.pswd=t2.pswd
from @t t1
cross apply(select top 1 pswd from @t t where t1.nm=t.nm order by id)t2

select * from @t
[/code]

PBUH

munami
Starting Member

2 Posts

Posted - 2010-11-08 : 06:14:03

quote:

Originally posted by Sachin.Nand


declare @t table(id int identity,nm varchar(30),pswd varchar(30))
insert @t
select 'max','123' union all
select 'max','678' union all
select 'max','900' union all
select 'tania','1200' union all
select 'tania','666' 

select * from @t

update t1 set t1.pswd=t2.pswd
from @t t1
cross apply(select top 1 pswd from @t t where t1.nm=t.nm order by id)t2

select * from @t

PBUH

Sachin . This is exactly what i want . Thanks a lot

webfred , this is part of integration. its sample data , we wanted to know the technique behind of this ... where we got our answer.

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 06:19:09

You are welcome.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources