Delete orphaned one way lookup data

Question

I have a set of records supplier__c which have a lookup to an address__c record.

So I'd like to gather all those up:

//Make a list of ID's of addresses on Suppliers : supadrs
List<Id> supadrs = [ SELECT Address__c FROM Supplier__c ];

Then lets gather all the address__c ids:

//Make a list of all address IDs : adrids
List<Id> adrids = [ SELECT Id FROM Address__c ];

And let's find all the address ids which are not on a supplier record:

//Make a list of ID's in List 2 which are not in List 1 and assign to List 3
list< Id > deletelist =[ SELECT Id FROM adrids where Id not in:supadrs ];

And then delete the list:

delete deletelist;

What would be the correct syntax for this action?

Answer 1

You can do this all in a single query. In fact, you can do this in a single line of code:

delete [SELECT Id FROM Address__c WHERE Id NOT IN (SELECT Address__c FROM Supplier__c)];

The second query automatically maps the Address__c values to the Id, allowing for an efficient query that doesn't involve any extra lines of code or clever manipulation.

Answer 2

You want a anti-join here:

SELECT Id FROM Address__c WHERE Id NOT IN (SELECT Address__c FROM Supplier__c)

That will give you all Address records that are not looked up to from any Supplier.