Multiple table deletion

Posted on 2011-04-19
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)
  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'

Question by:Frias-JH
    LVL 28

    Accepted Solution

    I don't see a delete for TableB in your query above - is it missing? Also, I wouldn't do your deletions one at a time - I'd do them in bulk (unless you're concerned about the overwhelming number you'd have, for some reason). Also, is the delay in there for a reason, or just so you can watch what it's doing, one step at a time?

    If I understand your query, you can do it in bulk, in a couple of steps, so it would be significantly faster overall:

      INTO #FileIdToDelete
      FROM TableA
     WHERE DownloadDate <=GETDATE()-90)
    DELETE b
      FROM TableB b
      JOIN #FileIdToDelete f
        ON b.FileID = f.FileID
    DELETE TableA
     WHERE DownloadDate <=GETDATE()-90)
      DROP TABLE #FileIdToDelete

    Open in new window


    Author Comment

    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'.
      INTO #FileIdToDelete
     WHERE DownloadedDate <=GETDATE()-90
    DELETE b
      FROM dbo.Images b
      JOIN #FileIdToDelete f
        ON b.ImageID = f.ImageID
     WHERE DownloadDate <=GETDATE()-90)
      DROP TABLE #FileIdToDelete

    Open in new window


    Author Closing Comment

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

    Your sollution was perfect. Thanks a lot

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now