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

1019833

I'm new to SQL and I really need help with this assignment, I'm not sure how or where to start.

Assignment details:

• Delete all data related to a record, but not the record itself.
• must accept input parm
• There is is RI in the tables so I must delete children first

Marwim

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

1004386

DELETE FROM TABLE_NAME A

WHERE ROWID<>(SELECT MAX(ROWID)

FROM TABLE_NAME B

WHERE A.COLUMN_NAME=B.COLUMN_NAME);

Purvesh K

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.

Santhosh T

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

Not sure by this, If you delete all the data from a record, how the record will be exists.

must accept input parm

delete from emp where empno=&empno;

This will promt you for the input value.

There is is RI in the tables so I must delete children first

By querying the USER_CONS_COLUMNS table you can get the constraint names on a table and then you can act according to it.

Shitab_416

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

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.

Purvesh K

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.

BluShadow

PurveshK wrote:


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.

Indeed.  Whichever way, it's not safe SQL, relying on ROWID's, and certainly does not do what the OP was asking.

Vmkk Viswanath Kanteti-Oracle

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.