Delete all data related to a record, but not the record itself.

Hello,

you should give us more information about your tables etc. Most of as have no crystal ball to see things not mentioned in your post ;-)

Regards

Marcus

You must be look out for Foreign Keys, which will cascade the Delete operation to its Children. But this will require you to delete the Parent record, which you do not want to delete.

Hence, the only option that you are left with is to manually write the Delete statements starting with the Tables that appear at the bottom of the hierarchy and moving upwards.

I did prepare a dynamic solution once, but not sure if you can adopt it for your situation. please check https://forums.oracle.com/thread/2483825 for the provided solution.

Hi,

First check the table refrentials. and delete the data from child tables based on key column.

then delete the data from main table.

to pass parameters in query use  (:bind Variable)

for ex.

delete from emp where empno = :empno;

Thanks

BluShadow wrote:


1004386 wrote:

DELETE FROM TABLE_NAME A
WHERE ROWID<>(SELECT MAX(ROWID)
FROM TABLE_NAME B
WHERE A.COLUMN_NAME=B.COLUMN_NAME);


That is a potentially dangerous piece of SQL you have provided.
That will likely delete all records from the table.
 

Or maybe, it won't delete any record from the Table if the data in Column is unique, which to me does not look like what OP is looking for.

I guess ur requirement is something like this. Let sat I have employees table and EMPID is the primary key, Now u wanted to delete all the remaining table which has empid column for an employee not deleting the main employee base table ? Am i Correct, If that is the requirement Here u go:

  SELECT 'delete from '||TABLE_NAME||' where '||COLUMN_NAME||'=&emp_id;

  ' from user_tab_cols where column_name='EMPNO' and table_name<>'EMP';

The above select lists u the delete statements that u need to perform. U need to modify the column name nad table name value in the aboce query for ur requirement.