SQL Server Performance Forum – Threads Archive

Deletion Problem

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

Simple enough … DELETE FROM master_table m
WHERE NOT EXISTS (SELECT * FROM child_table c WHERE c.fk_column is m.rk_column)

<pre id="code"> WHERE c.fk_column is m.rk_column </pre id="code"> …is…? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Time for weekend. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

… A G R E E D !!!

>>WHERE c.fk_column is m.rk_column No problem for NULL values [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

… 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)

What sql dialect are you using?[:0]

Mirko, If anything, it must be Dutch SQL.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] With an occasional bit of Jet SQL thrown in for good measure. I fully admit that I didn’t test the scripts, but what’s so funny about my dialect?

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan Mirko, If anything, it must be Dutch SQL.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] With an occasional bit of Jet SQL thrown in for good measure. I fully admit that I didn’t test the scripts, but what’s so funny about my dialect? <hr height="1" noshade id="quote"></blockquote id="quote">Somehow, I believed that even before you admited [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Seriously, I haven’t installed mssql server 2005 yet, but I doubt is can be used instead of = .

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

‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>