Deletion Script in Oracle needed to remove 1100 records from upto 10tables.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Deletion Script in Oracle needed to remove 1100 records from upto 10tables.

  1. #1
    Join Date
    Feb 2006
    Location
    RESTON, BA
    Posts
    1

    Talking Deletion Script in Oracle needed to remove 1100 records from upto 10tables.

    How in Windows ENV do I create a script to perform roughly 1100 delete statements which are the return of an ODBC MS ACCESS query.

    I have a file that I've obtained from exporting the results of a query that shows I have 1100 bogus records in my database. (e.g. refference # 123456)
    However each due to foreign key relations, I have 10 tables that I need to probe due to the reference number may have a child process entry.

    So I have a file named refid_file with 1100 records.
    123456
    789456
    145678
    234567
    345678

    And I have 10 tables I have to delete from
    delete from table a where refid=(entry from refid_file);
    delete from table b where refid=(entry from refid_file);
    ..... until table #10

    Then I need to do this for all 1100...

    Anyone with help is greatly appreciated. On a unix box I would write a k-shell script to take care of this..

    Running ORACLE 10G on Windows 2003 Server; access it by web-browser isql-plus.

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Code:
    On a unix box I would write a k-shell script to take care of this..
    Write a batch program in windows then..
    I did not get what you exactly asked for?
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    It seems that the issue you might be facing is how to delete records and cascade the deletes. That really depends on your schema though. If your schema does not cascade the deletes, then you need to write the sql such that the child tables that have no child tables get deleted first, and gradually work your way up the dependency tree. Looking at user_dependencies/dba_dependencies should help you to figure out the order in which to do the delete. Just don't start deleting stuff if you don't feel comfortable writing sql, and always check your backups first.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width