CODEWITHSUNDEEP
sqloracle-databasesql-delete
Marcus
Marcus

Reputation: 3869

How to delete data from specified tables at once in oracle sql

I have more than 40 tables in RATOR_MONITORING schema for which the table name is starting from 'TEMP_'. I want to delete data from all such tables at once in a single query instead of using delete statement for each and every table. I dont even want to generate statements. I can create anonymous block if required but dont know how to do that. I tried below query but its not working.

Delete from RATOR_MONITORING_CONFIGURATION.'%TEMP_';

Upvotes: 1

Views: 312

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

If you want to delete all the rows, then better use TRUNCATE, it will reset the high watermark. But remember, truncate is a DDL statement, and thus there will be an implicit commit; With DELETE you can commit manually after validation.

Although, I would not do that in a production environment. If it is something you are doing in test environment to build test data, then you could (ab)use EXECUTE IMMEDIATE.

For example, execute the following anonymous block as RATOR_MONITORING user:

DECLARE
  v_sql VARCHAR2(100);
BEGIN
  FOR i IN
  (SELECT table_name FROM user_tables where table_name like 'TEMP%'
  )
  LOOP
    v_sql := 'TRUNCATE TABLE '||i.table_name;
    EXECUTE immediate v_sql;
  END LOOP;
END;
/

By the way, using a good text editor, it won't take more than a minute to build DELETE/TRUNCATE statements and do it in pure SQL.

Upvotes: 1

Related Questions