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

Question

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.

A [mcve] is a great start when asking for SQL assistance. – jarlh May 22 '23 at 12:09 — jarlh, May 22 '23 at 12:09

score 2 · Answer 1 · answered May 22 '23 at 12:45

DELETE with ROWID IN works great in most cases. Another option that can utilize parallelism if your data is massive is MERGE:

MERGE /*+ enable_parallel_dml parallel(8) */ INTO portraitowner.TRAVELER_CC_AUTHENTICATION tgt
USING (SELECT /*+ parallel(8) */ tcca.ROWID AS row_id       
         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) src
 ON (src.row_id = tgt.ROWID)
WHEN MATCHED THEN UPDATE SET tgt.LAST_MODIFIED_TS = tgt.LAST_MODIFIED_TS
                  DELETE WHERE 1=1;

This allows you to use hash joins in the SELECT portion and feed the results into parallelized delete step. Unfortunately it does require a bogus UPDATE SET on a random column in order to satisfy the syntax requirements and get to the DELETE which happens within the UPDATE subprogram of MERGE.

score 1 · Answer 2 · answered May 22 '23 at 12:17

In general, you can correlate on the ROWID pseudo-column (which is effectively a pointer to the location of the row in the data file).

So the query would be:

DELETE FROM portraitowner.TRAVELER_CC_AUTHENTICATION
WHERE  ROWID IN (
  SELECT tcca.ROWID
  FROM   <your big join query>
  WHERE  <your filter conditions>
)

You may be able to remove some/all of the LEFT OUTER JOINs but if the query is reasonably performant and is returning the selection of rows that you want to remove then it may be more hassle to modify the query and re-validate the output than it is just to use the sub-query as-is.

score 1 · Accepted Answer · answered May 22 '23 at 12:27

1

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.

answered May 22 '23 at 12:27

Bogdan Dincescu

683
6

This did worked perfectly and deleted the actual records that I needed. Thanks. I will study this implementation as I have another similar issue with another table and this would be a great working example, I haven't seen this anywhere so far. Great to know it could be implemented the way I intended too. Much appreciated. Thanks – iamjoshua May 22 '23 at 12:47
btw, do you have a documentation somewhere where this is discussed?. I'm curious too as to whats the no. 1 mean for from the select query. – iamjoshua May 22 '23 at 12:49
This will not perform well unless all those join columns are indexed. The EXISTS will require a nested loops operation for every candidate row in tcca. You won't be able to use hash joins on the inner SELECT. – Paul W May 22 '23 at 13:28
Hi @PaulW . Can you elaborate like in laymans term?. I initially understood this as it would only delete 1 record at a time and had to redo the sql script again to delete remaining records. However upon revising this posted answer on another table I am having another issue with. It deleted 500k+ rows of records that I needed to be deleted. Upon reinserting a sample record, it did delete that single record thats needed to be deleted by then, hence as from what I could see on my side, it is working. Do I need to consider something else? – iamjoshua May 22 '23 at 14:04
1

This answer is not incorrect, it just won't perform well with large amounts of data, because Oracle would have to perform all these joins repeatedly for every row in your ttca table. That's what a correlated subquery (EXISTS) does. An uncorrelated (IN) is much better because it need only execute once, so can perform the table joins in a more optimal fashion. Look at the other answers, both MT0's and mine, for alternatives that work a lot better with bulk deletes. – Paul W May 22 '23 at 14:13
I'll try to check you answer too and make it work with what I need. Problem is I already deleted 500k+ records and hence I could not make a test of this large amounts of data performance. It did take like give or take 30 seconds for the query above to delete those 500k records, but it did what I need it to be. Thanks for your inputs @PaulW – iamjoshua May 22 '23 at 14:19
1

Sounds like your data volume is small enough any of our answers will work. – Paul W May 22 '23 at 14:20
@Paul W. Oftentimes the optimizer transforms the query into an in when there is an exists and does a hash join instead of executing the subquery for each row in the main query. I do not know if this will be the case for the particular tables given here. Should see the explain-plan and the execution plan for that SQL on that particular DB. – Bogdan Dincescu May 22 '23 at 14:22
Oh I see. This is also the first time such deletion is made on this table since it was made. If thats 500k is small enough on the greater scheme of things. I guess I could make use of the working query above then now?. Its just that this deletion of records will be done on a daily basis anyway as it will be placed on a spring batch job, so we don't expect hundreds of thousands of records to be deleted every day, at least as for what I know so far atm. @PaulW – iamjoshua May 22 '23 at 14:24
@Bogdan, yes, it can in certain circumstances, especially in later versions, but not always. I wouldn't rely on the optimizer rewriting like that. It's trying to rewrite less-than-optimal coding patterns; it's always better to correct the code instead. – Paul W May 22 '23 at 14:50

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

3 Answers3