SQL Server Performance

Deletion Problem

Discussion in 'SQL Server 2005 General Developer Questions' started by pallavi, Mar 17, 2006.

  1. pallavi New Member

    Hi All,

    I want to delete recrods from the tables which are linked with foreign key constraint, I have already used "On Delete Cascade ".

    When i delete the recrod from Master table all the recrods from Child table get deleted , but what I want to do is to delete the record from master table which is not referenced by any of the child recrods.

    For example :
    these are the two table
    Master : CompanyTypes (ID int, TypeName varchar(100)
    child : Company(Id int, Name varchar(100), TypeId int ,.....)

    I have given the "on delete cascade" on Company.TypeID.

    When I delete the recrod from CompanyTypes, related records from Company gets deleted,
    But what I want is : when I delete the recrods from company for perticular typeid then the recrod from Companytypes should also get deleted.

    Is there any way using which I can do this, i.e using some kind of constratints or somthing at design level.

    Deleting these recrods manually using delete queries is the last option.

    Pls do let me know abt ur suggestions and comments.

    Warm Regards!
    Pallavi
  2. Adriaan New Member

    Simple enough ...

    DELETE FROM master_table m
    WHERE NOT EXISTS (SELECT * FROM child_table c WHERE c.fk_column is m.rk_column)
  3. FrankKalis Moderator

    <pre id="code"><font face="courier" size="2" id="code"><br />WHERE c.fk_column is m.rk_column<br /></font id="code"></pre id="code"><br />...is...? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Time for weekend. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  4. Adriaan New Member

    ... A G R E E D !!!
  5. Madhivanan Moderator

    &gt;&gt;WHERE c.fk_column is m.rk_column<br /><br />No problem for NULL values [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  6. Adriaan New Member

    ... to return to the original question ...

    The query, now with your table and column names, is:

    DELETE FROM CompanyTypes CT
    WHERE NOT EXISTS (SELECT * FROM Company C WHERE C.TypeId is CT.Id)



    If you don't care much about the CompanyType values, you could add a trigger in Company;

    CREATE TRIGGER Company_CleanUpCompanyType ON dbo.Company
    FOR UPDATE, DELETE AS

    IF EXISTS
    (SELECT CT.Td FROM CompanyTypes CT
    WHERE NOT EXISTS (SELECT * FROM Company C WHERE C.TypeId is CT.Id))
    BEGIN
    DELETE FROM CompanyTypes CT
    WHERE NOT EXISTS (SELECT * FROM Company C WHERE C.TypeId is CT.Id)
    END

    GO

    ... but you really shouldn't be this careless with foreign keys! How many entries does the CompanyType table have anyway?

    You would be better of giving the users a query/view that tells them which CompanyType entries are not in use:

    SELECT CT.* FROM CompanyTypes CT
    WHERE NOT EXISTS (SELECT * FROM Company C WHERE C.TypeId is CT.Id)
  7. mmarovic Active Member

    What sql dialect are you using?[:0]
  8. Adriaan New Member

    Mirko,<br /><br />If anything, it must be Dutch SQL.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] With an occasional bit of Jet SQL thrown in for good measure.<br /><br />I fully admit that I didn't test the scripts, but what's so funny about my dialect?
  9. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Mirko,<br /><br />If anything, it must be Dutch SQL.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] With an occasional bit of Jet SQL thrown in for good measure.<br /><br />I fully admit that I didn't test the scripts, but what's so funny about my dialect?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Somehow, I believed that even before you admited [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] <br /><br />Seriously, I haven't installed mssql server 2005 yet, but I doubt <b>is</b> can be used instead of <b> = </b>.
  10. Adriaan New Member

    [xx(][xx(][xx(][xx(][xx(][xx(]<br />Like Frank said, it was Friday - time to go home. I didn't even spot the IS problem - aarrrghhhhhhhh!<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page