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)

  • 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

  • Roland,

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

  • 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

  • 'Simple' is best

  • 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
  • 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