Solved

MS SQL Syntax

Posted on 2013-12-03
6
180 Views
Last Modified: 2013-12-04
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?
0
Comment
Question by:slimguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39693901
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

0
 
LVL 15

Accepted Solution

by:
gplana earned 100 total points
ID: 39693905
Sorry, I made a mistake: please remove the b.  from my previous solution:

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

Open in new window

0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39693906
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
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 15

Expert Comment

by:gplana
ID: 39693927
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.
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39693943
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

0
 

Author Closing Comment

by:slimguy
ID: 39694870
Many thanks, I should have seen that myself - got a mental block last night!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question