| Tracking records deleted [message #40861] |
Fri, 08 November 2002 06:20  |
AA
Messages: 11
Registered: December 2001
|
Junior Member |
|
|
I have 2 tables A and B. A has about 100,000,000 records. The number of records in B varies but is typically in the 50-100k range. I run a SQL statement to delete records from A that are in B. Once in a while, it is possible that a few records in B are not in A. Is there a way I can log the records that were not deleted ?
The query that I run to delete is :
delete from A where (nvl(PART_CD,'X'), nvl(TYPE,'X'), nvl(QUAL_TYPE,'X'), nvl(QUALIFIER,'X')) in (select nvl(PART_CD,'X'), nvl(TYPE,'X'), nvl(QUAL_TYPE,'X'), nvl(QUALIFIER,'X') from B)
Currently I tried running a trigger that will delete a record from B whenever the same record is deleted from A, in that case, the remainder of the records in B are the ones that did not go through. But this process ran overnight and has not yet finished.
Any help would be highly appreciated.
Thanks
|
|
|
|
| Re: Tracking records deleted [message #40862 is a reply to message #40861] |
Fri, 08 November 2002 07:13  |
F. Tollenaar
Messages: 64
Registered: November 2002
|
Member |
|
|
create table temp as
select *
from b
where exists
(select *
from a
where (nvl(PART_CD, 'X'), nvl(TYPE,'X'), nvl(QUAL_TYPE,'X'), nvl(QUALIFIER,'X'))
in (select nvl(PART_CD,'X'), nvl(TYPE,'X'), nvl(QUAL_TYPE,'X'), nvl(QUALIFIER,'X') from B))
/
select *
from b
minus
select *
from temp
/
hth
Frank
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
