Multiple table deletion
Posted on 2011-04-19
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'