Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Which table join should I use Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 4:05 AM
Points: 20, Visits: 55
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)
Post #1394584
Posted Monday, December 10, 2012 6:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 429, Visits: 332
ron.grace (12/10/2012)
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)


DELETE Despatch
WHERE ordno NOT IN
(SELECT ordno FROM Order)



Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Post #1394587
Posted Monday, December 10, 2012 6:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 4:05 AM
Points: 20, Visits: 55
Roland,

That worked a treat, many thanks, I was obviously over complicating things
Post #1394591
Posted Monday, December 10, 2012 6:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 429, Visits: 332
ron.grace (12/10/2012)
Roland,

That worked a treat, many thanks, I was obviously over complicating things


LOL! I've outsmarted myself many times, I know what you mean!


Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Post #1394594
Posted Monday, December 10, 2012 7:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 4:05 AM
Points: 20, Visits: 55
'Simple' is best
Post #1394598
Posted Monday, December 10, 2012 7:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
I would recommend you get into the habit of qualifying your columns names

DELETE FROM Despatch
WHERE Despatch.ordno NOT IN
(SELECT Order.ordno FROM Order)




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1394599
Posted Monday, December 10, 2012 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 4:05 AM
Points: 20, Visits: 55
Gail

Will do, thanks for the tip, don't want to get into bad habits this early
Post #1394604
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse