How to delete documents from Filetable?

Question

I am trying to delete some documents from sql server's filetable.

Here I have one table in which I am storing all my Attachment's details and Documents in sql server's file table named Attchemnts.

AttachmentDetails table has below schema,

CREATE TABLE [dbo].[AttachmentDetails](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [DocumentName] [nvarchar](max) NULL,
    [DocumentType] [nvarchar](max) NULL,
    [ModifiedDateTime] [datetime] NOT NULL,
    [CreatedDateTime] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](254) NULL,
    [ModifiedBy] [nvarchar](254) NULL,
    [IsDeleted] [bit] NULL,
    )

Whenever I am uploading any document to File table then I am inserting that document's detailed information in AttchemntsDetails table as per table schema.

Here I have tried the below solution

CREATE PROCEDURE [dbo].[DeleteFiles] 
AS
BEGIN
DELETE Attachments
FROM AttachmentDetails a
WHERE 
    DocumentType = 'video/mp4'  AND  DATEDIFF(day, a.CreatedDateTime, GETDATE())<11
end

This procedure suppose to delete only Video/mp4 files who are 10 days older But it deletes any type of document from the filetable.

Answer 1

SQL is a set-based language. For every cursor/loop based script there's a far simpler and faster set based solution. In any case, the way this query is written would result in random deletions since there's no guarantee what all those TOP 1 queries will return without an ORDER BY clause.

It looks like you're trying to delete all video attachments older than 30 days. It also looks like the date is stored in a separate table called table1. You can write a DELETE statement whose rows come from a JOIN if you use the FROM clause, eg:

DELETE Attachments
FROM Attachments inner join table1 a on a.ID=Attachments.ID
WHERE 
    DocumentType = 'video/mp4' AND
    CreatedDateTime < DATEADD(day,-30,getdate())

EDIT

The original query contained DATEADD(day,30,getdate()) when it should be DATEADD(day,-30,getdate())

Example

Assuming we have those two tables :

create table attachments (ID int primary key,DocumentType nvarchar(100))

insert into attachments (ID,DocumentType)
values
(1,'video/mp4'),
(2,'audio/mp3'),
(3,'application/octet-stream'),
(4,'video/mp4')

and

create table table1 (ID int primary key, CreatedDateTime datetime)

insert into table1 (ID,CreatedDateTime)
values
(1,dateadd(day,-40,getdate())),
(2,dateadd(day,-40,getdate())),
(3,getdate()),
(4,getdate())

Executing the DELETE query will only delete the Attachment with ID=1. The query

select *
from Attachments
```

Will return :

```
ID  DocumentType
2   audio/mp3
3   application/octet-stream
4   video/mp4
```