asked on

MS SQL Syntax

I am trying to delete orphan records where the parent has been deleted (there is no FK defined).

This works:

select * from tblBVMeterReadingErrors a where not exists (Select 0 from tblBVMeterReadings b
where a.BV_Meter_Reading_ID = b.BV_Meter_Reading_ID);

But this doesn't:

delete from tblBVMeterReadingErrors a where not exists (Select 0 from tblBVMeterReadings b
where a.BV_Meter_Reading_ID = b.BV_Meter_Reading_ID);

What is wrong please?

gplana

Try this way:

delete from tblBVMeterReadingErrors where BV_Meter_Reading_ID NOT IN (Select BV_Meter_Reading_ID from tblBVMeterReadings
where b.BV_Meter_Reading_ID IS NOT NULL);

Open in new window

ASKER CERTIFIED SOLUTION

gplana

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ButlerTechnology

Select * from tblBVMeterReadingErros 
where bv_meterReading_ID not in (Select bv_MeterReadingID from tblBVMeterReading)

Delete from tblBVMeterReadingErros 
where bv_meterReading_ID not in (Select bv_MeterReadingID from tblBVMeterReading)

Open in new window

The above code should work.
Tom

gplana

ButlerTechnology, this is exactly my solution. I just only add a filter to avoid NULL values which some times could cause problems in comparisions inside the NOT IN.

QuinnDex

this should do it for you check you are getting the correct results with the select before using the delete

select * from tblBVMeterReadingErrors where not in(Select 0 from tblBVMeterReadings
where tblBVMeterReadingErrors .BV_Meter_Reading_ID = tblBVMeterReadings.BV_Meter_Reading_ID);

Open in new window

delete from tblBVMeterReadingErrors where not in(Select 0 from tblBVMeterReadings
where tblBVMeterReadingErrors .BV_Meter_Reading_ID = tblBVMeterReadings.BV_Meter_Reading_ID);

Open in new window

slimguy

ASKER

Many thanks, I should have seen that myself - got a mental block last night!