self join - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

self join

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-06-24 : 11:12:32

Hi,
Please let me know how to do this update...

table1 has several fields (ID, field1, field2, field3)

I would like to run an update to update field3 such that when field1 and field3 match then use the ID value and place it in Field3

I think this is to do with self join.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-24 : 11:16:34

UPDATE table1 SET Field3 = [ID] WHERE Filed1 = Field3

Doesn't make a lot of sense though...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-24 : 11:17:52

Update yourTable
Set field3 = id
From yourTable
Where field1=field3

or
Update yourTable
Set field3 = case when field1=field3 then id else field3 end
From yourTable

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-06-24 : 11:27:09

quote:
Originally posted by Seventhnight

Update yourTable
Set field3 = id
From yourTable
Where field1=field3

or
Update yourTable
Set field3 = case when field1=field3 then id else field3 end
From yourTable

Hi,
I don't think this would work because:
lets say field3 has a value of 12345
Then you need to look for 12345 in field1
and then get the ID value

Thanks
Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-06-24 : 11:30:57

Hi,
I don't think this would work because:
lets say field3 has a value of 12345
Then you need to look for 12345 in field1
and then get the ID value

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-24 : 11:41:18

are we missing your objective? Looks like it works to me:


set nocount on
declare @tb table ([id] int, col1 int, col2 int, col3 int)
insert @tb 
select 1,20,3,40 union all
select 2,30,3,30 union all
select 3,20,3,20 union all
select 4,21,3,40

select * from @tb
update @tb set col3 = [id] where col1=col3
select * from @tb

Be One with the Optimizer
TG

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-06-24 : 11:51:56

Hi,
Apologiese if my question is not quite clear.I think I should make this more clear.
let say you have:

ID FeedID ManagerID
1 65 96
2 45 67
3 96 12

As you can see, ManagerID = 96 is as same as the FeedID = 96 and because of this I would like to take the ID = 3 and place 3 instead of 96 in field ManagerID

Hope this is more clear.
Thank you all

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-24 : 13:04:35

ok, hows this?


set nocount on
declare @tb table ([id] int, feedid int, Managerid int)
insert @tb
select 1, 65, 96 union
select 2, 45, 67 union
select 3, 96, 12

select * from @tb
update a set
	a.ManagerID = b.id
from	@tb a
join	@tb b
		on a.ManagerID = b.FeedID
select * from @tb

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources