Delete all rows which name does not appear on a column

Question

I have 2 SQL tables as follows:

create table dbo.Posts (
  Id int identity not null,
  Content nvarchar (max) not null
  -- other columns
)

create table dbo.Files (
  Id int identity not null,
  Name nvarchar (200) not null
  -- other columns
)

I need to delete all files where its Name does not appear inside the Content column of any Post ... Note, that Content has HTML code where the files Names are somewhere included.

For example, if File with Name=hello-john is not found in Content of any Post then it should be deleted.

UPDATE

I tried a Select to test it but somehow this gives me all files so I am not sure what am I missing:

select Name
from dbo.Files
where Name NOT IN (SELECT p.Content FROM dbo.Posts as p)

Am I missing something?

Possible duplicate of [Delete sql rows where IDs do not have a match in another table](http://stackoverflow.com/questions/3384127/delete-sql-rows-where-ids-do-not-have-a-match-in-another-table) — R Day, Dec 09 '15 at 11:16
In this case I need to look inside the Content column of each post for the file name ... The content has inside HTML string ... — Miguel Moura, Dec 09 '15 at 11:17
Then you can adapt the `JOIN` clause as appropriate for your situation. — R Day, Dec 09 '15 at 11:19

score 1 · Accepted Answer · answered Dec 09 '15 at 11:27

Finding all Files-rows that are not referenced:

SELECT * 
FROM Files f 
WHERE NOT EXISTS (
    SELECT 1 FROM Posts p WHERE p.content like '%' + f.name + '%');

Deleting all of these:

DELETE
FROM Files
WHERE NOT EXISTS (
    SELECT 1 FROM Posts p WHERE p.content like '%' + Files.name + '%');

or

DELETE FROM Files where id in
(SELECT f.id 
FROM Files f 
WHERE NOT EXISTS (
    SELECT 1 FROM Posts p WHERE p.content like '%' + f.name + '%'));

Might be slow, depending on the number of rows..

Delete all rows which name does not appear on a column

1 Answers1