Deleting from multiple SQL tables in SQL developer-CodePudding

I am trying to delete data from multiple tables using one code. The two connecting factors for all these tables is that the start and ending of the names are the same so as an example, table 1 is BRIGHTEXT, BRITGEXT. The second factor is that the dates are the same in both tables. I want to delete where table name is like BRI%EXT and the startdate > date

I have used the

delete from tablename where date >

but this would only work for one table. How can I make it work for multiple tables?

CodePudding user response：

You can't. DELETE works on one table.

If you can use PL/SQL, then write a procedure which will e.g. loop through USER_TABLES (you'd name all of them or use wildcard - as you stated) and - with dynamic SQL (execute immediate) - delete rows based on that condition.

Alternatively, if you create a view as union of appropriate column names from all those tables, you could then write an instead of trigger; you'd delete from a view, while trigger would delete from its underlying tables.

CodePudding user response：

Oracle supports deleteing from multiple tables at the same time

DELETE (SELECT *
        FROM table1 t1
        INNER JOIN table2 t2
            ON t2.ref_id = t1.id
        WHERE t1.date > '2022-01-01'
            AND t2.date > '2002-01-01')

But for this to work you must first find a SELECT that includes aöll rows