Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Deleting Values from a table based on an id from another?

I have two tables:

Data
Orders

I want to delete any data which does not belong to an order.

So for example my ID's could be

Orders    |     Data
------------------------
700001   |     700001
700002   |     700002
700003   |     700003
Null         |     700004
Null         |     700005
Null         |     700006

As you can see Data contains 3 orders which do not exist in the Orders Table, this is because an order has been canceled. I would like to cleanup the Data table using a delete, but am uncertain as to how I do joins within a delete (or if it's needed).

Cheers
0
directxBOB
Asked:
directxBOB
1 Solution
 
chaitu chaituCommented:
delete from data d
where

not exists (select 'x' from orders or
where or.orderid=d.orderid
);
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what database? (optimal) syntax can vary...
0
 
directxBOBAuthor Commented:
SQL on a windows mobile device
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Pratima PharandeCommented:
delete from data d
where
d.id not in
 (select orders.id from orders
);

here id means the firld whick contains 700001,700002 etc
0
 
directxBOBAuthor Commented:
Mobile SQL doesn't appear to like Sub Queries
0
 
directxBOBAuthor Commented:
More digging

select * from data d
where

not exists (select * from orders or
where or.orderid=d.orderid);

Appears to work, but it doesn't work when using a delete.
0
 
Pratima PharandeCommented:
delete from data
where

not exists (select * from orders
where orders.orderid=data.orderid);
0
 
Pratima PharandeCommented:
delete from data
where
data.id not in
 (select orders.id from orders
);
0
 
directxBOBAuthor Commented:
How would I rewrite this:

delete from data
where
data.id not in
 (select orders.id from orders);

using a join?
0
 
Pratima PharandeCommented:
As I know delete query not work with joins
0
 
directxBOBAuthor Commented:
http://support.microsoft.com/kb/303004

This appears to explain the issue of using subqueries within a delete.
0
 
Pratima PharandeCommented:
Use with(nolock) with subquery which will stop locking the table

delete from data
where
data.id not in
 (select orders.id from orders with(nolock));

0
 
directxBOBAuthor Commented:
Cheers for all the help.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now