Ambiguous join / 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

Transact-SQL (2000)

Ambiguous join / self-join

Author

Topic

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2003-08-26 : 13:07:46

OK; my brain hurts. Can anyone help make this work -- it involves a joined update with a self-join. I'm getting an error 8154 "The table mytable is ambiguous" error. I've included DDL and insert statements -- basically, what do I need to do to make this less ambigous? (maybe it's a job for SNL's Ambiguously Gay Duo, but then I digress...)

UPDATE mytable
SET name = m2.name
FROM mytable m1
INNER JOIN mytable m2 ON m1.id = m2.id
WHERE m2.set_id = 2

CREATE TABLE mytable (id INT, name VARCHAR(10), set_id INT)

INSERT mytable VALUES (1,'test',1)
INSERT mytable VALUES (2,'test',2)
INSERT mytable VALUES (2,'test2',2)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-26 : 13:43:37

Here ya go:

UPDATE m1
SET name = m2.name
FROM mytable m1
INNER JOIN mytable m2 ON m1.id = m2.id
WHERE m2.set_id = 2

Tara

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2003-08-26 : 14:21:04

quote:
Originally posted by tduggan

Here ya go:

UPDATE m1
SET name = m2.name
FROM mytable m1
INNER JOIN mytable m2 ON m1.id = m2.id
WHERE m2.set_id = 2

Tara

THANK YOU! I am eternally grateful!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources