title: How can I run a delete query that will skip records that are referenced by another table's foreign key?
body: <p>I have an application that does a large number of searches by querying remote APIs. The results are pulled into my data tier (SQL Server) where they sit to see if the user would like to further interact with them. Once the user starts working with that item, it's essential that it stays in the system, but the rest of the results are entirely useless and just taking up space.</p>
  
  <p>I was thinking about making a task that would run a sproc to delete any rows that are NOT referenced by another table. Is there a way to do that?</p>
  
  <p>Another way of wording it would be: Is there a way to execute a delete statement that would skip rows that would cause an error due to a violation of referential integrity?</p>
  
  <p>EDIT: Thanks to new info from @Kos...
  The alternate path I'm considering is to add a bit column to the table and mark it true if the row ends up being used and just having my task delete rows marked false.</p>
  
  <p>For clarity, here is an overview of the situation. The syntax might not be perfect on these, but hopefully you get the idea:</p>
  
  <pre><code>-- Where all the results get pulled down and held (The table I want to clean up every 2 hours or so)
  CREATE TABLE [reservations].[DumpTable](
      [utypeID] [bigint] IDENTITY(1,1) NOT NULL
      -- Other columns
      CONSTRAINT [PK_UnitTypesFound] PRIMARY KEY CLUSTERED 
      (
          [utypeID] ASC
      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  
  -- This is one of many other tables that might reference the dump table
  CREATE TABLE [reservations].[OtherTables](
      [memberID]  INT NOT NULL,
      [utypeID]   BIGINT NOT NULL -- Need to Know if the dumptable is referenced here
      CONSTRAINT [PK_MemberUnitTypes] PRIMARY KEY CLUSTERED
      (
          [memberID],
          [utypeID]
      ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  ALTER TABLE [reservations].[OtherTables]  WITH CHECK ADD  CONSTRAINT [FK_OtherTable_DumpTable] FOREIGN KEY([utypeID])
      REFERENCES [reservations].[DumpTable] ([utypeID])
  
  ALTER TABLE [reservations].[OtherTables] CHECK CONSTRAINT [FK_OtherTable_DumpTable]
  </code></pre>