How to Delete Records NOT IN

sql sql-server sql-delete
Jobert Enamno picture Jobert Enamno · Feb 19, 2013 · Viewed 46.5k times · Source

Hi I have the following SQL Query which gives me Scheme_Id which exist both in ProjectSchemes and Schemes table. I want to delete all records from Schemes table which have no record to ProjectSchemes table. How can I do so? Please help. I'm using MSSQL

select scheme_id from Schemes where Scheme_Id
in(select s.Scheme_Id from Projects p 
inner join ProjectSchemes ps on ps.Project_Id=p.Project_Id
inner join Schemes s on s.Scheme_Id=ps.Scheme_Id)

I'm trying to do the following but it's not working. Not working means no records affected but as I checked my Schemes table there are so many records that their scheme_id cannot be found on the ProjectSchemes table 

delete from Schemes where Scheme_Id
not in(select s.Scheme_Id from Projects p 
inner join ProjectSchemes ps on ps.Project_Id=p.Project_Id 
inner join Schemes s on s.Scheme_Id=ps.Scheme_Id)

Answer

S.Roeper picture S.Roeper · Feb 19, 2013

I would like to start with assumptions.

  1. You have a chainlike data model: Projects --* ProjectSchemes --* Schemes
  2. Your target is to have only valid chains, so no ProjectSchemes without Project, no Schemes without ProjectSchemes.
  3. NULL is not a valid value for one of your ids.
  4. All ids are unique in their table
  5. You don't use referential integrity mechanisms of your database

As a result your SELECT would list the scheme_id for all Schemes in the Schemes table.

Said that, you should start to delete all ProjectSchemes without a corresponding Project. These are ProjectSchemes with an id of NULL or an id which does not exists in the Projects Table:

DELETE ProjectSchemes WHERE (Project_Id is NULL) OR 
(NOT EXISTS (SELECT * FROM Projects WHERE
             Projects.Project_Id = ProjectSchemes.Project_Id))

After deleting the ProjectsSchemes without a Project we now may have some new orphans in the Schemes Table. The next thing is now to delete all Schemes which have an id of NULL or an id which does not exists in the ProjectsSchemes Table:

DELETE Schemes WHERE (Scheme_Id is NULL) OR 
(NOT EXISTS (SELECT * FROM ProjectSchemes WHERE
             ProjectSchemes.Scheme_Id = Schemes.Scheme_Id))

There is still a chance to have schemes which are not connected to a project without deleting the ProjectSchemes.

Related questions

How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to delete the top 1000 rows from a table using Sql Server 2008?

I have a table in SQL Server. I would like to delete the top 1000 rows from it. However, I tried this, but I instead of just deleting the top 1000 rows it deleted all the rows in the table. Here is …

Read More
SQL DELETE with INNER JOIN

There are 2 tables, spawnlist and npc, and I need to delete data from spawnlsit. npc_templateid = n.idTemplate is the only thing that "connect" the tables. I have tried this script but it doesn't work. I have tried this: DELETE …

Read More