Delete rows older than 2 months and cascade down to 3 tables

Question

I have the following SQL Scheme on a SQL Server 2014 database:

create table dbo.Media ( 
  Id int identity not null
    constraint PK_Media_Id primary key clustered (Id),
  Created datetime not null,
)

create table dbo.MediaFile (
  MediaId int not null, 
  FileId int not null
)    

create table dbo.[File]
(
  Id int identity not null
    constraint PK_File_Id primary key clustered (Id),
  Content varbinary (max) filestream null
    constraint DF_File_Content default (0x)
) filestream_on [FILE]

alter table dbo.MediaFile
add constraint FK_MediaFile_MediaId foreign key (MediaId) references [Media](Id) on delete cascade on update cascade,
    constraint FK_MediaFile_FileId foreign key (FileId) references [File](Id) on delete cascade on update cascade;

I am deleting all Media rows older than 2 months using the following:

DELETE 
FROM dbo.Media
WHERE Created < GETDATE() - 60

This deletes the MediaFile because I have on Delete Cascade but does not delete the correspondent files ...

How can I delete Media, their MediaFiles and their files for rows in Media older than 2 months?

It looks like you are adding both constraints to the same table. Try adding a constraint on the File table. — Joe C, Feb 17 '17 at 12:44
[Also, don't use dates arithmatic like this. Use DateAdd instead.](https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations) — Zohar Peled, Feb 17 '17 at 12:49
On second look it appears you are setting up a many to many relationship. Here is a related question. http://stackoverflow.com/questions/1379333/how-to-cascade-delete-over-many-to-many-table — Joe C, Feb 17 '17 at 12:53

score 0 · Answer 1 · answered Feb 17 '17 at 13:53

This will delete all Files where related Media are going to be deleted, unless they are also related to Media that are not going to be deleted.

delete f 
from dbo.[File] as f
  inner join dbo.MediaFiles as mf
     on mf.FileId = f.FileId
  inner join dbo.Media as m
     on m.MediaId = mf.MediaId
    and m.Created < dateadd(day,-60,getdate())
  where not exists (
      select 1
      from dbo.Media as m
        inner join dbo.MediaFiles as mf
          on m.MediaId = mf.MediaId
         and m.Created >  dateadd(day,-60,getdate())
      where mf.FileId = f.FileId
  );

delete 
from dbo.Media
where Created <  dateadd(day,-60,getdate());

This will delete all Files where related Media are going to be deleted.

delete f 
from dbo.[File] as f
  inner join dbo.MediaFiles as mf
     on mf.FileId = f.FileId
  inner join dbo.Media as m
     on m.MediaId = mf.MediaId
    and m.Created <  dateadd(day,-60,getdate());

delete 
from dbo.Media
where Created < dateadd(day,-60,getdate());

using exists() instead:

delete f 
from dbo.[File] as f
where exists (
  select 1 
  from dbo.Media as m
    inner join dbo.MediaFiles as mf
      on m.MediaId = mf.MediaId
     and m.Created <  dateadd(day,-60,getdate())
  where mf.FileId = f.FileId
  );

delete 
from dbo.Media
where Created <  dateadd(day,-60,getdate());

score -1 · Answer 2 · edited May 23 '17 at 12:09

-1

If you want a more general solution, look here. However for you case, this should do

DELETE FROM MediaFile WHERE MediaID IN (
   SELECT Id FROM Media WHERE Created < dateadd(dd, -60, GetDate())
)

DELETE FROM Media WHERE Created < dateadd(dd, -60, GetDate())

DELETE FROM File WHERE NOT EXISTS (SELECT 1 FROM MediaFile WHERE File.Id = FileId)

edited May 23 '17 at 12:09

Community

1
1

answered Feb 17 '17 at 12:48

Marko Juvančič

5,792
1
25
41

Delete rows older than 2 months and cascade down to 3 tables

2 Answers2