Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94

    Unhappy Unanswered: Deletes From Multiple Tables.

    Hello everybody,

    We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
    We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.

    Is it possible? Please advice.

    Thanks in advance.

    Regards,

    Ashish
    In GOD we believe. Everything else we Test!

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    use "Cascade Delete"...
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    I'm sorry i forgot to mention that the Cascading Deletes are not allowed.

    Even wrtting a trigger to delete from all the tables will mean writing some kind of loop or Cursor if we use the system tables.
    I can use sys tables but the delete qury using sys tbales needs to execute in 1 go. No cursors or temp tables should be used.

    Is there any other way?
    In GOD we believe. Everything else we Test!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by wash
    I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
    Frickin' morons.
    My advice is to go work for a company that isn't managed by idiots.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Now there's the blind dude we all know and love
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No cascading deletes allowed.
    No temp tables allowed.
    No cursors allowed. (Well, at least they are on the right track with that one...)

    Absolute amateurs.

    What advice would you give them?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    What advice would you give them?
    Well, it realy sounds like a request for homework...but

    Code:
    DECLARE @FKey sysname, @keyvalue varchar(8000), @sql varchar(8000)
    SELECT @FKey = 'EmployeeID', @keyValue = '1', @sql = ''
    
        SELECT @sql = @sql + 'DELETE FROM ['+o.name+'] WHERE '+@FKey+'='+@KeyValue+' GO '
          FROM sysreferences r 
     LEFT JOIN sysobjects o 
    	ON r.fkeyid = o.id
    WHERE rkeyid =  OBJECT_ID('Employees')
    
    SELECT @sql
    
    --EXEC(@sql)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Thanks dear friends.

    Brett, it's not a homework at all.

    In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.

    Me being the modest developer cannot argue with the people who have set these guidelines. Please forgive those poor souls for their ignorance.

    Thanks once again.
    In GOD we believe. Everything else we Test!

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by wash
    In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things
    Using cascading deletes is the best solution for this problem (and is supported by all major DBMS).
    If the "world largest" project does not apply the most effectice solutions to such a basic problem, then I'm pretty sure it is going to fail. This is extremely short-sighted and will create more maintenance trouble then they probably are willing to pay for. Why not use flat files, if they don't want to use the features of a database?
    (nothing personal against you wash, it's just that I see this kind of stupid decisions all around...)

  10. #10
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Man, i don't mind it as long as they are paying me for the job. []

    I do not exactly know what has prompted the TAs to avoid cascade deletes.
    In GOD we believe. Everything else we Test!

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by wash
    In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
    reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Quote Originally Posted by Thrasymachus
    reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.
    Forgive me but i don't work on any such project.
    In GOD we believe. Everything else we Test!

  13. #13
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    I have had everyone in my team at my company, including the so called SQL experts working with me, brainstorm on this issue.

    It seems without a recursive loop , we cannot achieve this.

    Can you give me any idea if we can achieve this using a simple loop.

    I want to delete all the dependent records to the Nth level.

    Thanks in advance.
    In GOD we believe. Everything else we Test!

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    HAve one of your "sql experts" get in on this thread...and did you even look at my code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jan 2004
    Location
    Tacoma, Wa
    Posts
    15
    What in the world would drive such requirements? It makes as much sense as "We'd like to retrieve all the data from the database but I can't reference any table names or use the word 'select'".

    I'm also guessing that you're going to generate a heap of log activity with this puppy, so you might want to think about doing a set of tables at a time or a set of primary key values, unless you own unlimited disk.

Posting Permissions

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