InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 17:28:34 GMT20http://www.sqlservercentral.com/Forums/Topic1394584-1292-1.aspxGailWill do, thanks for the tip, don't want to get into bad habits this earlyMon, 10 Dec 2012 07:14:26 GMTron.gracehttp://www.sqlservercentral.com/Forums/Topic1394584-1292-1.aspxI would recommend you get into the habit of qualifying your columns names[code="sql"]DELETE FROM DespatchWHERE Despatch.ordno NOT IN(SELECT Order.ordno FROM Order)[/code]Mon, 10 Dec 2012 07:02:45 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1394584-1292-1.aspx'Simple' is bestMon, 10 Dec 2012 07:00:40 GMTron.gracehttp://www.sqlservercentral.com/Forums/Topic1394584-1292-1.aspx[quote][b]ron.grace (12/10/2012)[/b][hr]Roland,That worked a treat, many thanks, I was obviously over complicating things[/quote]LOL! I've outsmarted myself many times, I know what you mean!Mon, 10 Dec 2012 06:58:52 GMTRoland Alexander STLhttp://www.sqlservercentral.com/Forums/Topic1394584-1292-1.aspxRoland,That worked a treat, many thanks, I was obviously over complicating thingsMon, 10 Dec 2012 06:56:59 GMTron.gracehttp://www.sqlservercentral.com/Forums/Topic1394584-1292-1.aspx[quote][b]ron.grace (12/10/2012)[/b][hr]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 suggestionsdelete from Despatch(select b.ordno from Despatch bfull outer join Order aon b.ordno = a.ordnowhere a.ordno is null)[/quote][code]DELETE DespatchWHERE ordno NOT IN(SELECT ordno FROM Order)[/code]Mon, 10 Dec 2012 06:50:31 GMTRoland Alexander STLhttp://www.sqlservercentral.com/Forums/Topic1394584-1292-1.aspxI'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 suggestionsdelete from Despatch(select b.ordno from Despatch bfull outer join Order aon b.ordno = a.ordnowhere a.ordno is null)Mon, 10 Dec 2012 06:46:17 GMTron.grace