CODEWITHSUNDEEP
sqlsql-servert-sql
Miguel Moura
Miguel Moura

Reputation: 39374

Delete all rows which name does not appear on a column

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?

Upvotes: 0

Views: 213

Answers (1)

Tobb
Tobb

Reputation: 12205

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

Upvotes: 1

Related Questions