How to modify a select sql query to do a delete query with oracle db?

Advertisements

I need some help in my sql script. I initially patterned it with our sql excel report so that I could also see the rows that would be deleted with my select query. After modifying it based on the requirements. I could finally see the appropriate rows that needs to be deleted.

However, apparently, converting a select query to delete query is hard. I am already a novice in sql scripting again as I have not been using sql scripts/commands for almost 2 years now. Can anyone help me convert this select query below to a delete query?.

SELECT  
tcca.*             
FROM 
portraitowner.business_traveler bt INNER JOIN portraitowner.client_org_unit cou ON bt.client_org_unit_top_id = cou.client_org_unit_id
AND     bt.client_org_unit_top_orig_id = cou.client_org_unit_orig_id INNER JOIN portraitowner.client_org_unit cous ON bt.client_org_unit_sub_id = cous.client_org_unit_id
AND     bt.client_org_unit_sub_orig_id = cous.client_org_unit_orig_id LEFT JOIN portraitowner.traveler_e_mail_address tema ON bt.business_traveler_id = tema.business_traveler_id
AND     bt.business_traveler_orig_id = tema.business_traveler_orig_id AND tema.e_mail_address_type_code = 1 INNER JOIN portraitowner.traveler_credit_card tcc ON bt.business_traveler_id = tcc.business_traveler_id
AND     bt.business_traveler_orig_id = tcc.business_traveler_orig_id INNER JOIN portraitowner.TRAVELER_CC_AUTHENTICATION tcca ON tcc.TRAVELER_CREDIT_CARD_ID = tcca.TRAVELER_CREDIT_CARD_ID
AND     tcc.TRAVELER_CREDIT_CARD_ORIG_ID = tcca.TRAVELER_CREDIT_CARD_ORIG_ID LEFT JOIN portraitowner.TRAVELER_CC_ROLLUP tcr ON tcc.traveler_credit_card_id = tcr.traveler_credit_card_id
AND     tcc.traveler_credit_card_orig_id = tcr.traveler_credit_card_orig_id
AND     bt.business_traveler_id = tcr.traveler_cc_auth_id
AND     bt.business_traveler_orig_id = tcr.traveler_cc_auth_orig_id
WHERE 
cous.localization_country_code = 'IN'
AND 
tcc.LAST_MODIFIED_TS > tcca.LAST_MODIFIED_TS
AND SYSDATE - 7 > tcca.LAST_MODIFIED_TS

What the script above does is query from tcc table and search for related child record in the tcca table.
tcc is like a record of form of payments
tcca is like the records of tokens.
We need to delete the tokens from tcca table when the form of payment is updated which makes the tcca tokens more older than the ones in the tcca table. We delete this matching conditions after 7 days. Now as for the inner joins etc this came from the original sql report and I am not sure if this will still be needed for the delete query. But one thing I am sure is that this select query is what I already need for deleting records on the tcca table as I could see the rows that will be deleted with the conditions set here.

Problem is, I can’t quite convert it to a delete query. Already followed the delete from * in ([select query is placed here]) here etc. Yet I can’t make it work too there as I dont know to place on the tables from delete query.

>Solution :

delete from  portraitowner.TRAVELER_CC_AUTHENTICATION tcca
where SYSDATE - 7 > tcca.LAST_MODIFIED_TS
  and exists (
    select 1
    FROM portraitowner.business_traveler bt 
    INNER JOIN portraitowner.client_org_unit cou ON bt.client_org_unit_top_id = cou.client_org_unit_id
      AND     bt.client_org_unit_top_orig_id = cou.client_org_unit_orig_id 
    INNER JOIN portraitowner.client_org_unit cous ON bt.client_org_unit_sub_id = cous.client_org_unit_id
      AND     bt.client_org_unit_sub_orig_id = cous.client_org_unit_orig_id 
    LEFT JOIN portraitowner.traveler_e_mail_address tema ON bt.business_traveler_id = tema.business_traveler_id
      AND     bt.business_traveler_orig_id = tema.business_traveler_orig_id AND tema.e_mail_address_type_code = 1 
    INNER JOIN portraitowner.traveler_credit_card tcc ON bt.business_traveler_id = tcc.business_traveler_id
      AND     bt.business_traveler_orig_id = tcc.business_traveler_orig_id 
    LEFT JOIN portraitowner.TRAVELER_CC_ROLLUP tcr ON tcc.traveler_credit_card_id = tcr.traveler_credit_card_id
      AND     tcc.traveler_credit_card_orig_id = tcr.traveler_credit_card_orig_id
      AND     bt.business_traveler_id = tcr.traveler_cc_auth_id
      AND     bt.business_traveler_orig_id = tcr.traveler_cc_auth_orig_id
    WHERE cous.localization_country_code = 'IN'
      AND tcc.LAST_MODIFIED_TS > tcca.LAST_MODIFIED_TS      
      and tcc.TRAVELER_CREDIT_CARD_ID = tcca.TRAVELER_CREDIT_CARD_ID
      AND tcc.TRAVELER_CREDIT_CARD_ORIG_ID = tcca.TRAVELER_CREDIT_CARD_ORIG_ID

);

I’d go for this one delete statement which would perform better than actually fetching in PL/SQL the rowid for each row in that select and deleting row by row, or, eventually bulk collecting with a limit and deleting in bulks.

Leave a ReplyCancel reply