tags:

views:

340

answers:

3

Hi Folks,

Maybe I'm missing a bracket or something but I'm having a hard time deleting rows from a table variable where I'm left joining and looking for the key I'm joining on. If it has a value, then I get rid of it. The problem is that I can't get the query to parse. Any ideas?

declare @MrTemp ( key1 int ,key2 int )

insert into @MrTemp select key1, key2 from ASourceTable

delete
from @MrTemp mt left join ARealTable art on mt.key1 = art.key1 and mt.key2 = art.key2 where art.key1 is not null and art.key2 is not null

+1  A: 
DELETE @MrTemp
FROM @MrTemp mt LEFT JOIN ...
John Saunders
A: 

You need to reference the alias after the delete but before the from for the table you want to delete from

delete art 
from @MrTemp mt left join ARealTable art on 
mt.key1 = art.key1 and mt.key2 = art.key2 
where art.key1 is not null and art.key2 is not null
cmsjr
A: 

You can only delete from one table at a time:

to delete from @MrTemp [where there is a matching record in ARealTable]

delete mt
from @MrTemp mt left join ARealTable art on mt.key1 = art.key1 and mt.key2 = art.key2 where art.key1 is not null and art.key2 is not nu

ll

or. to delete from ARealTable [again where the record has a corresponding record in ARealTable]

delete art
from @MrTemp mt left join ARealTable art on mt.key1 = art.key1 and mt.key2 = art.key2 where art.key1 is not null and art.key2 is not null
Dog Ears