Error when joining on 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 2000 Forums

SQL Server Development (2000)

Error when joining on same table

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-14 : 10:11:47

When I issue the following statement:

UPDATE oms.OrderLineUnit
SET BundleOrderLineUnit = T2.OrderLineUnitID
FROM oms.OrderLineUnit T1 INNER JOIN oms.OrderLineUnit T2 ON T1.OrderID = T2.OrderID
AND T1.CatalogueItemReference = T2.CatalogueItemReference
AND T1.Sequence = T2.Sequence
AND T1.TPNB IS NOT NULL AND T2.TPNB IS NULL

I get this error message:

Msg 8154, Level 16, State 1, Line 229
The table 'oms.OrderLineUnit' is ambiguous.

I know I can use a CTE, but is there a way to to resolve this using only alias's??

Thankyou

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 10:14:52

[code]UPDATE T1~~oms.OrderLineUnit~~
SET T1.BundleOrderLineUnit = T2.OrderLineUnitID
FROM oms.OrderLineUnit T1
INNER JOIN oms.OrderLineUnit T2 ON T2.OrderID = T1.OrderID
AND T2.CatalogueItemReference = T1.CatalogueItemReference
AND T2.Sequence = T1.Sequence
AND T2.TPNB IS NULL
AND T1.TPNB IS NOT NULL[/code]

Peter Larsson
Helsingborg, Sweden

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-14 : 10:21:48

Thanks again Peter!!!!!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources