January 6, 2008 at 9:17 pm
I have a view who's resulting rows contain the info of records I need to delete from a certain table. How do I mass delete records from TableA based on the results from my view?
My logic...
For each record in MyView
Delete from TableA
Where
MyTable.ColA = MyView.ColA AND
MyTable.ColB = MyView.ColB AND
MyTable.ColC = MyView.ColC
Hopefully this is clear enough.... Thanks for your help!
January 6, 2008 at 9:26 pm
Actually you're pretty much there. It should look something like this:
delete tableAlias1
from Table1 as tableAlias1
inner join MyView
on TableAlias1.a=MyView.a and
TableAlias1.b=MyView.b and
TableAlias1.c=MyView.c and
TableAlias1.d=MyView.d
No need for any loop - the view will handle it.
Note: ANSI actually recommends that you use an EXISTS syntax instead of a JOIN. The syntax would then look like:
delete
from Table1 as tableAlias1
WHERE
Exists
(
select *
from MyView
where
TableAlias1.a=MyView.a and
TableAlias1.b=MyView.b and
TableAlias1.c=MyView.c and
TableAlias1.d=MyView.d
)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 6, 2008 at 9:45 pm
From: Mr. Miller
To: Mr. Miller
That would be EXACTLY what I need it to do. My gracious Thank You!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply