Transferring data retaining self jon relationship

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)

Transferring data retaining self jon relationship

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-08 : 06:50:56

I have four tables, two from which I have to select (OrderLine and LineItem), and two in which i have to insert the results of the select (SavedForLaterOrderLine, SavedForLaterLineItem).

One of the tables has a self join (OrderLine) and I need to retain this relationship in the table SavedForLaterORderLine. I have mocked up a couple of similar scaled down tables:


use Master
go
drop database TestJoins
go
create database TestJoins
go
use testjoins
create table SavedForLaterOrderLine
(
  SavedForLaterID int identity primary key
, CatalogueItemID int
, ParentSavedForLaterID int
, OrderLineID int
, ParentOrderLineID int
)
create table SavedForLaterLineItem
(
  SavedForLaterID int foreign key references SavedForLaterOrderLine(SavedForLaterID)
, SavedForLaterLineItem int identity Primary key
, TPNB int
)

create table OrderLine
(
  OrderLineID int identity primary key
, CatalogueItemID int
, parentOrderLineIDID int
)
create table LineItem
(
 OrderLineID int foreign key references OrderLine(OrderLineID)
, LineItemID int identity Primary key
, TPNB int
)
go

insert into OrderLine (CatalogueItemID, parentOrderLineIDID)
select 123,null
insert into LineItem (OrderLineID, TPNB)
select OrderLineID, 3333333 from OrderLine where OrderLineID = 1
insert into LineItem (OrderLineID, TPNB)
select OrderLineID, 4444444 from OrderLine where OrderLineID = 1

insert into OrderLine (CatalogueItemID, parentOrderLineIDID)
select 134,1
insert into LineItem (OrderLineID, TPNB)
select OrderLineID, 1111111 from OrderLine where OrderLineID = 2
insert into LineItem (OrderLineID, TPNB)
select OrderLineID, 2222222 from OrderLine where OrderLineID = 2

select t1.OrderLineID 
, CatalogueItemID 
, parentOrderLineIDID 
, TPNB 
into #temp 
from OrderLine t1 inner join LineItem t2 on t1.OrderLineiD = t2.OrderLineID

select * from #temp

insert into SavedForLaterOrderLine
(
  CatalogueItemID 
, ParentSavedForLaterID 
, OrderLineID 
, ParentOrderLineID 
)

--select ... from OrderLine 

insert into SavedForLaterLineItem
(
 OrderLineID 
, TPNB 
)

--select ... from LineItem

I really don't know how to preserve the parent/child relationship in the SavedForLaterOrderLine table using the primary/foreign key from OrderLine?? Am I making sense?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-08 : 09:06:31

The only method i have come up with is to insert the data, and then update the table after the insert, like so:


insert into SavedForLaterOrderLine
(
  CatalogueItemID 
, ParentSavedForLaterID
, OrderLineID 
, ParentOrderLineID 
)

select distinct CatalogueItemID 
, null 
, OrderLineID 
, ParentOrderLineIDID
from #temp

update SavedForLaterOrderLine 
set ParentSavedForLaterID = SavedForLaterID
from SavedForLaterOrderLine t1 inner join SavedForLaterOrderLine t2
on t1.ParentOrderLineID = t2.OrderLineID

However, i keep getting an error which I can't resolve.

Msg 8154, Level 16, State 1, Line 1
The table 'SavedForLaterOrderLine' is ambiguous.

I am sure there must be a way to achieve what I am trying to do without having to do an insert and update. I would appreciate any help on this.

Thankyou

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-08 : 09:18:02

ok, I have discovered that if you perform an update referencing the same table more than once in the from clause, at least one of them has to have no alias. Thats fine! But now its updating it with the wrong SavedForLaterID argghhhhh............

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-08 : 10:50:00

This works.

update SavedForLaterOrderLine
set ParentSavedForLaterID = t2.SavedForLaterID
from SavedForLaterOrderLine inner join SavedForLaterOrderLine t2
on t2.OrderLineID =SavedForLaterOrderLine.ParentOrderLineID

But any other suggestions where I don't have to do a separate update would be greatly appreciated!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources