Delete statement not working on query in oracle

Question

I have a table BK_178_ABC. I want to perform delete statement on this table only when some condition is satisfied:

My total count on this table is:

select count(*) from BK_178_ABC; ==>22024727

I have this condition below,if it satisfies then I need to delete from this BK_178_ABC table.So,I first calculated what is the total no of rows going to be deleted.So,I tried the statement below.

SELECT Count(*)                                    
FROM
BK_178_ABC  a
    LEFT JOIN
        (SELECT * FROM xyz WHERE UPPER(type) = 'MOOV' AND UPPER(buyer) = 'KERA') gr
    ON
        SubStr (a.PS_UNIQUE_ID,-8)  = gr.SOURCEID
    LEFT JOIN
        (SELECT * FROM xyz WHERE UPPER(type) = 'MOOV' AND UPPER(buyer) = 'KERA') gr2
    ON
        a.GROUP_NBR   = gr2.SOURCEID
WHERE EXISTS
(
  SELECT 1 FROM droptable drp
  WHERE
    COALESCE(gr.ROLLUPGROUPID, gr2.ROLLUPGROUPID, SubStr(a.ps_unique_id,-8))=drp.groupid
);

So,the count to be deleted is : 2902563.

I tried two delete statement but,both of them gave me wrong count.Since,my expected count is 2902563,but I am not getting the correct result.

DELETE FROM bk_178_abc     WHERE  EXISTS (SELECT 1 
               FROM   bk_178_abc a 
                      LEFT JOIN (SELECT * 
                                 FROM   xyz 
                                 WHERE  Upper(type) = 'MOOV' 
                                        AND Upper(buyer) = 'KERA') gr 
                             ON Substr (a.ps_unique_id, -8) = gr.sourceid 
                      LEFT JOIN (SELECT * 
                                 FROM   xyz 
                                 WHERE  Upper(type) = 'MOOV' 
                                        AND Upper(buyer) = 'KERA') gr2 
                             ON a.group_nbr = gr2.sourceid 
               WHERE  EXISTS (SELECT 1 
                              FROM   droptable drp 
                              WHERE  COALESCE(gr.rollupgroupid, 
                                     gr2.rollupgroupid, 
                                     Substr(a.ps_unique_id, -8)) = drp.groupid)) 
; 

It,emptied the whole table, as total count deleted I see is : 22024727

So,I refactored this above code again,tried another delete statment:

 DECLARE
    
          begin
            DELETE FROM BK_178_ABC  a WHERE EXISTS (SELECT 1 FROM (SELECT * FROM xyz WHERE UPPER(type) = 'MOOV' AND UPPER(buyer) = 'KERA') gr,
             xyz gr2,
            droptable  drp  WHERE   SubStr (a.PS_UNIQUE_ID,-8)  = gr.SOURCEID AND a.GROUP_NBR   = gr.SOURCEID
              AND   COALESCE(gr.ROLLUPGROUPID, gr2.ROLLUPGROUPID, SubStr(a.ps_unique_id,-8))=drp.groupid );  
            Dbms_Output.put_line(SQL%ROWCOUNT);
            END;

I see that rowcount is zero. What is the problem in my sql statment?

Answer 1

You can use IN as follows:

DELETE FROM bk_178_abc     WHERE  rowid in (SELECT a.rowid
               FROM   bk_178_abc a 
                      LEFT JOIN (SELECT * 
                                 FROM   xyz 
                                 WHERE  Upper(type) = 'MOOV' 
                                        AND Upper(buyer) = 'KERA') gr 
                             ON Substr (a.ps_unique_id, -8) = gr.sourceid 
                      LEFT JOIN (SELECT * 
                                 FROM   xyz 
                                 WHERE  Upper(type) = 'MOOV' 
                                        AND Upper(buyer) = 'KERA') gr2 
                             ON a.group_nbr = gr2.sourceid 
               WHERE  EXISTS (SELECT 1 
                              FROM   droptable drp 
                              WHERE  COALESCE(gr.rollupgroupid, 
                                     gr2.rollupgroupid, 
                                     Substr(a.ps_unique_id, -8)) = drp.groupid)) 
;