• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Multiple table deletion

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
0
Frias-JH
Asked:
Frias-JH
  • 2
1 Solution
 
Ryan McCauleyCommented:
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:

SELECT DISTINCT FileID
  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

0
 
Frias-JHAuthor 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

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

Your sollution was perfect. Thanks a lot
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now