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.

`DELETE ... FROM ... WHERE ...` The storage type doesn't matter — Panagiotis Kanavos, Aug 12 '19 at 11:05
The query posted here would result in *random deletions* because there's *no* guarantee what all those `TOP 1` queries will return when there's no ORDER BY clause. What are you trying to do? It's a lot easier to write a proper `DELETE` query than this complicated loop. — Panagiotis Kanavos, Aug 12 '19 at 11:08
I wrote loop cause there i awant to check that condition for each record in the table. — Madhav, Aug 12 '19 at 11:23
That's what the `WHERE` clause in all queries is for. `WHERE DocumentType = 'video/mp4' AND CreatedDateTime < DATEADD(day,30,getdate())` checks all records for both conditions and only deletes those that match — Panagiotis Kanavos, Aug 12 '19 at 11:25
BTW post the table schemas, a sample of the data and the desired output. Right now people have to *guess* what those tables are. Is `ID` a primary key in both table's? What is `Table1`? — Panagiotis Kanavos, Aug 12 '19 at 11:50

Panagiotis Kanavos · Answer 1 · 2019-08-12T12:01:41.850

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
```

I tried above query and that have deleted all the documents from the filetable. — Madhav, Aug 12 '19 at 11:37
That would happen if *all* of them matched the criteria. If all attachments are videos older than 30 days, they'll be deleted. On the other hand, if the `ID` columns are misused and eg `table1.ID` is *not* unique, perhaps there's a matching `table1` record for every attachment. You didnt' provide any information about the tables, only an invalid query — Panagiotis Kanavos, Aug 12 '19 at 11:48
Actualy there were only 2-3 documents of type video rest of them were images, text and doc files. yeah I agree that i haven't provided table schema. — Madhav, Aug 12 '19 at 11:53

How to delete documents from Filetable?

1 Answers1