Solved

how to delete records that contain no child records

Posted on 2011-02-25
5
286 Views
Last Modified: 2012-06-27
I need to delete all order records that do not container order items.

the tables are linked by the orderId on the orderItems table. So i can do the following

select * from orders o
inner join orderitems oi on o.id = oi.orderid

How can i detect which order records contain no child orderItems
0
Comment
Question by:frosty1
5 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 34981543
delete from orders o where not exists(select * from orderitems oi where oi.orderid = o.orderid)
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34981566
backup your db before tyr
delete from orders where id not in
 (select orderid from orderitems )

Open in new window

0
 
LVL 3

Accepted Solution

by:
jmro20 earned 500 total points
ID: 34981586
Delete From Orders
          Left Join OrderItems On Orders.OrderId = OrderItems.OrderId
Where OrderItems.OrderId IS NULL      
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34982526
I would recommend using jmro20's answer.  Among other points in its favor is the fact that it is probably going to provide the best performance.
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34997724
Did you solved your problem?
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 44
Mysql Left Join Case 10 70
Currency in SQL? 2 30
Why do I get the message "Message has been thrown by target of an invocation"? 22 53
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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