Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2008 > SQL Server 2008
|
SQL Server 2008 General discussion of SQL Server *2008* version only - not related to a specific book.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2008 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 10th, 2012, 08:41 AM
Registered User
 
Join Date: Feb 2012
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Which table join should I use

I've got 2 tables Order & Despatch and I'm trying to add some keys to the tables. The OrderNo field is a primary key in Order and a foreign key in Despatch, but it won't let me add the key as there are obviously some records in Despatch that don't exist in Order. I now want to delete these rogue records. My first attempt was as follows but didn't work, any suggestions

delete from Despatch
(select b.ordno from Despatch b
full outer join Order a
on b.ordno = a.ordno
where a.ordno is null)
 
Old December 10th, 2012, 01:12 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

SELECT * FROM Despatch WHERE OrderNo NOT IN
(SELECT Ordno FROM Order)

This will show you all the rows in Despatch that don't have an OrdNo in Order.

Just so you can take a look and see if you really want to delete those rows.

Then you can run:

DELETE FROM Despatch WHERE OrderNo NOT IN
(SELECT Ordno FROM Order)
 
Old December 11th, 2012, 03:32 AM
Registered User
 
Join Date: Feb 2012
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Many thanks, worked a treat.
I was obviously over complicating things





Similar Threads
Thread Thread Starter Forum Replies Last Post
join two table with gridview yousof BOOK: Beginning ASP.NET 4 : in C# and VB 2 January 12th, 2011 03:41 AM
table join problem ankurkumar ASP.NET 3.5 Basics 4 July 27th, 2009 09:39 AM
table join debbiecoates SQL Server 2000 2 July 2nd, 2008 01:39 PM
can i make self join table seco MySQL 1 April 18th, 2007 11:17 PM
could i join function(return table) with a table alyeng2000 SQL Server 2000 6 September 30th, 2004 07:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.