Post reply

Which table join should I use

  • December 10, 2012 at 6:46 am

    #265882

    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)

  • December 10, 2012 at 6:50 am

    #1567011

    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 Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • December 10, 2012 at 6:56 am

    #1567015

    Roland,

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

  • December 10, 2012 at 6:58 am

    #1567018

    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 Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • December 10, 2012 at 7:00 am

    #1567022

    'Simple' is best

  • December 10, 2012 at 7:02 am

    #1567023

    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, MVP, M.Sc (Comp Sci)
    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

  • December 10, 2012 at 7:14 am

    #1567028

    Gail

    Will do, thanks for the tip, don't want to get into bad habits this early

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply