May 6, 2002 at 12:43 pm
I just ran into an interesting issue with SQL 2000... You can't do a joined update on a self join. It fails with: The table 'TableName' is ambiguous.
I know there are ways around the update I'm trying to perform like creating a temp table or possably even creating a view of the table with a different name to join against, but they just wouldn't be as efficient.
Here is an example of what I was trying:
Given a table similar to this:
create table dbo.emp (
emp_pkey int primary key not null,
parent_key int null,
data varchar(10) null)
insert into dbo.emp (emp_pkey,parent_key,data) values (1,null,'test1')
insert into dbo.emp (emp_pkey,parent_key,data) values (2,null,'test2')
insert into dbo.emp (emp_pkey,parent_key,data) values (3,1,null)
insert into dbo.emp (emp_pkey,parent_key,data) values (4,2,null)
go
update emp
set data = b.data
from
emp a join emp b on a.parent_key = b.emp_pkey
where b.data is null
If anyone knows how to do this without a temp table let me know.... I'll try a view and post back if it works.
Thanks,
Dan
May 6, 2002 at 1:15 pm
Change your statement
quote:
update emp
set data = b.data
from
emp a join emp b on a.parent_key = b.emp_pkey
where b.data is null
to:
update a
set data = b.data
from
emp a join emp b on a.parent_key = b.emp_pkey
where a.data is null
or
update b
set data =a.data
from emp a inner join emp b on a.emp_pkey = b.parent_key
where b.data is null
Edited by - EPol29 on 05/06/2002 1:51:56 PM
May 6, 2002 at 1:54 pm
Thanks!
I didn't realize you could use the alias there... works like a charm now.
Dan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply