How to Delete Records NOT IN
Solution 1
I would like to start with assumptions.
- You have a chainlike data model: Projects --* ProjectSchemes --* Schemes
- Your target is to have only valid chains, so no ProjectSchemes without Project, no Schemes without ProjectSchemes.
- NULL is not a valid value for one of your ids.
- All ids are unique in their table
- 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.
Solution 2
DELETE FROM schemes
WHERE scheme_id NOT IN (
SELECT DISTINCT scheme_id
FROM projectschemes
WHERE scheme_id IS NOT NULL
)
Or you can alternatively use
DELETE
FROM schemes
WHERE NOT EXISTS (
SELECT 1
FROM projectschemes
WHERE projectschemes.scheme_id = schemes.ID
)
Solution 3
DELETE s
FROM Schemes s LEFT JOIN ProjectSchemes ps ON s.Scheme_Id=ps.Scheme_Id
WHERE ps.Scheme_Id IS NULL
But sounds like you need this
DELETE sp
FROM ProjectSchemes sp LEFT JOIN Schemes s ON sp.Scheme_Id=s.Scheme_Id
WHERE s.Scheme_Id IS NULL
Comments
-
Jobert Enamno almost 2 years
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)
-
a_horse_with_no_name about 11 yearsGood catch. And gets around the problem with
NULL
s andNOT IN
as well. -
Jobert Enamno about 11 yearsI got error "Incorrect syntax near 's'." the WHERE has red underline. I'm using MSSQL
-
Jobert Enamno about 11 yearsI tried this query but it says "(0 row(s) affected)" maybe no records found ps.Scheme_Id IS NULL? ProjectScheme.Scheme_Id has records and there's no entry that has NULL on it. It happened that both Schemes and ProjectSchemes were not enforce by FK that's why there are so many records on projectschemes that have no entry on Schemes table.
-
Aleksandr Fedorenko about 11 yearsIf this query you say (0 row(s) affected) then it means that in Schemes table not records which unassociated with ProjectSchemes table
-
Aleksandr Fedorenko about 11 yearsMaybe you want delete records from ProjectSchemes table which unassociated with Schemes table?
-
Jobert Enamno about 11 yearsWorks like a charm!!!! You really understand the problems. Thanks for the help now I was able to get rid those records!!!
-
Drew over 7 years2nd half of this is a visual of cascading deletes to children stackoverflow.com/a/32298405