I have to delete the specific rows from oracle table. Below is the table structure.

1. For Item Id 1234 where more than 1 row with Cancel state exists it should delete last 2 rows of Cancel State such that only two records remains in table with 02/Apr/2021 to 04/June/2021 and 05/June/2021 to 06/June/2021(also 06/June/2021 gets updated to 31/Dec/3033). I have to identify all the rows where more than 1 Cancel State along with one Active state exists and then have to delete all the cancel rows except 1 cancel row and then have to update item end date to max date for that row.

2. For Item Id 1235, since only one row exits of Cancel state it should gets deleted from the table. I have to identify all such rows where state is only Cancel and no Active state row exists for that Item Id and then delete them.

3. For Item Id 1236, its a perfect state therefore nothing should happenes to such rows.

After deleting specific rows table would change to something like below

Please suggest pointers to get this done. Can this be done using sql alone ?I have not done coding in PL/SQL before.

Many many thanks in advance.

CodePudding user response：

Indeed it is possible in SQL. Here is what worked for me. The below snippet will hold details for Item IDs in active state:

create table active as 
select * 
from
(select id, state, count(*) as flg from itm group by id, state)
where state='Active';

Similarly, in the below snippet, creating a table for Item IDs in Cancel state:

create table cancel as 
select * 
from
(select id, state, count(*) as flg from itm group by id, state)
where state='Cancel';

Note that I've created flg variable which holds number of records with active and cancel respectively.

The below code snippet will ignore perfect Item IDs (i.e., 1236) for now.

create table target as
select a.id as a_id, a.state as a_state, a.flg as a_flg, c.* 
from active as a 
full join cancel as c 
    on a.id=c.id
where a.flg <> c.flg;

Below code will help us to get Item IDs that needs to be analysed.

create table ads as select *, count(*) as cnt 
from itm
where 
 id IN (select distinct a_id from target where a_id IS NOT NULL)
 or 
 id IN (select distinct id from target where id IS NOT NULL)
group by id 
order by id, state, start_date, end_date;

/* The below snippet is to delete any record with only cancel */

create table fin_ads as 
select * from ads
where cnt <> 1;

Below snippet will give us the final results

    /* The below snippet is to stitch records with multiple cancel */
    select id, state, min(start_date) as start format=date9., max(end_date) as end format=date9. 
from fin_ads
    group by id, state
    UNION
    /* UNION is used to combine the perfect Item IDs back to the result */
    select id, state, start_date as start, end_date as end from itm 
    where id IN
    (
    select distinct a.id
    from active as a 
    inner join cancel as c 
    on a.id=c.id
    where a.flg =c.flg)

I know this is not an optimized query and is long. Yet this gives the result as expected. Note: Use sub-queries to reduce number of intermediate tables getting created. Hope this helps! Do let me know if this works/ facing any issues.

CodePudding user response：

Do you want to delete and update rows in your table or merely select the described result from your table?

Then it looks like there is always at most one active row per item in the table. Your result hence shows the items with one row per status and their minimum start date and maximum end date.

The following simple query may hence already do what you want to do:

select
  item_id,
  item_state,
  min(item_start_date) as item_state_start_date,
  max(item_end_date) as item_state_end_date
from mytable
group by item_id, item_state
order by item_id, item_state;

This selects rows. If you want to delete and update rows instead, you can simpy create a table from the above result, delete rows in your original table and fill the table from the newly created one. (Or create the new table, drop the old one, rename the new one.)