Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to delete all the rows in the ContentData table that have the word 'DV95' in the htmldata column.

Here is what I am using:

BEGIN TRANSACTION

DELETE Contentdata
FROM Contentlinks INNER JOIN contenttasks 
   ON contentdata.contentid = contenttasks.contentid
WHERE contentdata.htmldata LIKE '%DV95%'

Here are my questions:

  1. Is there problem in the syntax?
  2. It keeps showing me a number of FK contraint issues due to number of table dependencies. How do I delete these rows?
share|improve this question
    
You can use SSMS's 'Parse' button (the blue check mark) to check the syntax without running the query. Also, you need to close your transaction with a commit. –  Nick Chammas Jan 11 '12 at 16:02
1  
That is how I got this error message. I didn't run the query yet. Thanks! –  wippo Jan 11 '12 at 16:04
add comment

1 Answer

If I got you right - you need to delete referencing rows prior to deleting the referenced rows.

begin transaction
DELETE Contenttasks
FROM contentdata
 where contentdata.contentid = contenttasks.contentid 
and contentdata.htmldata LIKE '%DV95%'

Delete from contentdata where htmldata like '%dv95%'
Commit
share|improve this answer
    
Well, yes! hope this helps -- Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'ON'. -- I got this error when running the query (contentdata, contenttasks and contentlinks are tables) –  wippo Jan 11 '12 at 15:53
    
by the way, i really appreciate your response and for your time. Thanks!! –  wippo Jan 11 '12 at 15:53
    
Oleg's answer is correct, but you may be able to make use of "ON DELETE CASCADE" in your tables definition. Search for "DELETE CASCADE" and your db engine of choice. –  adam f Jan 11 '12 at 22:20
    
Corrected the small mistake, try it. –  Oleg Dok Jan 12 '12 at 4:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.