Intelligent Delete Needed

[deepgrewal22]

I have a table with about 800,000 records and the records are organized as such:

PLANT_ID, PROJ_NUM, START_DATE, END_DATE

The problem that I am having is that some plants have more than one entry where the only difference is the dates. This is unnecessarily increasing the amount of records within this table.

*Unfortunately, the primary key is a combination of ALL of the fields. Therefore, no 2 records can be exactly the same at anytime.

WHAT IS THERE NOW:
------------------
PLANT1, 123456, 3/22/2007 6:00:00 AM, 5/11/2007 4:59:58 AM
PLANT1, 123456, 5/11/2007 4:59:59 AM, 1/2/2010 5:59:59 AM

WHAT IT SHOULD CONSOLIDATE TO:
------------------------------
PLANT1, 123456, 3/22/2007 6:00:00 AM, 1/2/2010 5:59:59 AM

*Note that the (earliest) start date from one record has been combined with the (latest) end date of the other record.

QUESTIONS:
----------
(1) Since the primary key is ALL of the fields, I cannot have two records that are exactly the same at any given time and need to delete one instance of a record. How can I perform the delete and complete the requirement outlined in the next question?

(2) How can I consolidate 2 different (records) sets of dates for a given PLANT_ID and PROJ_NUM into 1 (record) set of dates (keeping the earliest START_DATE, and latest END_DATE)?




Deep Grewal
Don't say the M word; you sound ignorant.

 

[SantaMufasa]

Deep,

Your problem is virtually identical to thread1177-1364531. The tightest solution seems to be Taupirho's solution at the end of that thread.

As far as how to delete the rows, I, personally, would

1) CREATE TABLE <new-table> to hold the results of Taupirho-style code,
2) TRUNCATE TABLE <your-original-table name>;
3) INSERT INTO <your-original-table name> FROM <new-table>;
4) DROP TABLE <new-table>;

The only problem comes if you have child records that already point to rows in <your-original-table>. If that is the case, then let us know since we will have to revise the approach to solving your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[deepgrewal22]

SantaMufasa,

Thanx for the results. I am brand new to PL/SQL and have only a basic knowledge of SQL itself. I will look through the coding. The difference between my problem and notadba's (from thread1177-1364531: PLSQL Help) is that I only have to deal with 1 table as opposed to two. I will see if I can try to understand the coding provided by you and taupirho and parse it so that it only deals with 1 table like the one I have.

If anyone can provide some direct coding related to my specific problem, it would be of great assistance. This request was placed upon me by upper management and I need to accomplish this as soon as humanly possible.



Deep Grewal
Don't say the M word; you sound ignorant.

 

[SantaMufasa]

Deep, Taupirho's solution doesn't evern refer to the second table; mine you can turn into a one-table solution simply by removing this code fragment:

, tab2 t2 where t1.code = t2.code

.

Let us know if either solution works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[deepgrewal22]

Mufasa,

I have removed the code that you specified and am working with the script today. Thanx again. I will keep you posted.


Deep Grewal
Microsoft Works - oxymoron