We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Multiple table deletion

Frias-JH
Frias-JH asked
on
Medium Priority
276 Views
Last Modified: 2012-05-11
I have 2 tables which I need to clean up. Table A has the timestamp and a file identifier, Table B has the file reference and the file identifier from Table A. I have part of it worked out but I could use a hand on the syntax. I am using rowcount=1 so that the query can find the entry on table a, get the file identifier then select the correct row on table B and then delete both rows on the tables. Is this the way to do this or is there a better idea? Here is what I have so far...

declare @RowsDeleted bigint
set @RowsDeleted = 0
while exists(select count(1) from <TABLE_A> where DownloadDate <=getdate()-90)
begin
  set rowcount 1
  *** Syntax to select the rows from TABLE_A and extract the fileid *****
  *** Syntax for deletion of row containing the fileid from above ***
  set @RowsDeleted = @RowsDeleted + 1
  print cast(@RowsDeleted as varchar) + ' rows deleted'
  waitfor delay '00:00:01'
end

TIA
Comment
Watch Question

Senior Data Architect
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Here is what I have. It is giving the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_Events_Images". The conflict occurred in database "*", table "dbo.Events", column 'ImageId'.
SELECT DISTINCT ImageID
  INTO #FileIdToDelete
  FROM dbo.events
 WHERE DownloadedDate <=GETDATE()-90
 
DELETE b
  FROM dbo.Images b
  JOIN #FileIdToDelete f
    ON b.ImageID = f.ImageID

DELETE acetaxicab.dbo.events
 WHERE DownloadDate <=GETDATE()-90)
 
  DROP TABLE #FileIdToDelete

Open in new window

Author

Commented:
I just switched it to delete from the other table first. Easiest solution I guess.

Your sollution was perfect. Thanks a lot
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.