Re: Multi Row DELETEs
Date: 1997/06/09
Message-ID: <339d2e1d.40919308_at_newshost>#1/1
On Mon, 09 Jun 1997 10:15:58 -0500, Programming <sysdev_at_mb.sympatico.ca> wrote:
>Multi-row DELETEs
>
>I am trying to do something conceptually very simple.
>
>I have 2 tables. I want to delete the records in one table,
>that don't exist in the other table.
>
[snip]
>
>delete from AGT
>where AGT_REF IN
>(select c.agt_ref
>from agt3 c
>minus
>select a.agt_ref
>from agt a )
>
Here you have said:
delete all rows in AGT
such that the AGT_REF column is in the set of AGT_REFS that
is all of the AGT_REFS in agt3 that are NOT in AGT
That is, you have a set of AGT_REFS from AGT3 that are not IN AGT by definition, none of the AGT_REFs in the subquery are in AGT. There are no rows to be deleted....
>0 rows deleted. Elapsed: 00:00:00.72
>
>
>Delete from AGT
>where AGT_REF IN
>(select c.agt_ref
>from agt3 c, agt b
>where c.agt_ref = b.agt_ref (+)
>and b.agt_ref is null )
>
Same here, you are outer joining AGT to AGT3, and only keeping the row if it does not exist in AGT. By definition, the set of AGT_REFs in the subquery cannot be in the AGT table, hence no records to delete.
>0 rows deleted. Elapsed: 00:00:00.80
>
>
>
>I DO have DELETE privileges on the AGT table.
>
To delete the records in AGT that are not in AGT3 you would:
delete from AGT
where not exists ( select NULL from agt3 where agt3.agt_ref = agt.agt_ref )
/
or
delete from AGT
where AGT_REF not in ( select agt_ref from agt3 )
/
>Am I missing something? Any ideas?
>
>
>Rodger Lepinsky
>ADP Systems Partnership
>Winnipeg, Manitoba, Canada
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 09 1997 - 00:00:00 CEST