Derrived tables in a delete - 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)

Derrived tables in a delete

Author

Topic

Teroman
Posting Yak Master

115 Posts

Posted - 2002-01-16 : 10:12:01

Hi,

ive got what i thought was quite a simple delete, looks a bit like this

delete myTable
from myTable T1
join (select foo, bar from otherTable) D1
on T1.foo = D1.foo
left outer join (select foo, bar from yetAnotherTable) D2
on T1.foo = D2.foo
where D2.bar is NULL

obviously the derrived tables and joins are more complex, but you get the idea.

if i parse it, no problems
if i change the delete line for a select * then it parses and runs fine
problem is if i try to compile the proc as above, it complains that D1 and D2 are not in the query, specifically

Server: Msg 107, Level 16, State 2, Procedure wn_FuelPriceChange_Move_Valid, Line 48
The column prefix 'D1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure wn_FuelPriceChange_Move_Valid, Line 48
The column prefix 'D2' does not match with a table name or alias name used in the query.

whats going on, any ideas?

its peeing me of quite a lot, i dont want to have to mess about with temp tables

thanks

col

Teroman
Posting Yak Master

115 Posts

Posted - 2002-01-16 : 10:47:53

problem solved, if anyone is interested you delete the alias, not the table, like this:

delete T1
from myTable T1
join (select foo, bar from otherTable) D1
on T1.foo = D1.foo
left outer join (select foo, bar from yetAnotherTable) D2
on T1.foo = D2.foo
where D2.bar is NULL

ta da!!

col

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources