Forum Stats

  • 3,760,128 Users
  • 2,251,647 Discussions
  • 7,870,992 Comments

Discussions

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

1019833
1019833 Member Posts: 1
edited Jun 26, 2013 7:44AM in SQL & PL/SQL

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

Answers

  • Marwim
    Marwim Member Posts: 3,644 Gold Trophy

    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
    1004386 Member Posts: 37

    DELETE FROM TABLE_NAME A

    WHERE ROWID<>(SELECT MAX(ROWID)

    FROM TABLE_NAME B

    WHERE A.COLUMN_NAME=B.COLUMN_NAME);

  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    edited Jun 26, 2013 3:02AM

    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
    Santhosh T Member Posts: 410 Bronze Badge

    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
    Shitab_416 Member Posts: 74

    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
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    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
    Purvesh K Member Posts: 2,319 Silver Trophy
    edited Jun 26, 2013 6:52AM
    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
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    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.

  • 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.

This discussion has been closed.