Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

query problem _ delete unmatch items from table

I am drawing a blank - I have two tables worth of data where I need to remove unmatached from table1.

I need to find all records from table 1(TA_FTIR) that doesn't exists in table2 (TD_FTIR) and remove them from table1.  

My current query seems to return only those records that are equal, I tried changing the join and the placement of the is Null without success.  What am I missing?

SELECT TA_FTIR.FTIR_MeasNo, TD_FTIR.FirstOfFTIR_BusName
FROM TD_FTIR LEFT JOIN TA_FTIR ON TD_FTIR.[FirstOfFTIR_BusName] = TA_FTIR.[FTIR_MeasNo]
WHERE (((TD_FTIR.FirstOfFTIR_BusName) Is Not Null));

Open in new window

0
Karen Schaefer
Asked:
Karen Schaefer
  • 2
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
use this query

SELECT TA_FTIR.FTIR_MeasNo, TD_FTIR.FirstOfFTIR_BusName
FROM TD_FTIR LEFT JOIN TA_FTIR ON TD_FTIR.[FirstOfFTIR_BusName] = TA_FTIR.[FTIR_MeasNo]
WHERE (((TD_FTIR.FirstOfFTIR_BusName) Is Null));
                                 

to delete those records, use this query

Delete * from
TA_FTIR , TD_FTIR
Where TA_FTIR.[FTIR_MeasNo] Not In
 (Select [FirstOfFTIR_BusName]  From TD_FTIR)
0
 
Karen SchaeferAuthor Commented:
Delete * from
TA_FTIR , QT_ARINC_BusDnld
Where TA_FTIR.[FTIR_MeasNo] Not In
 (Select [FirstOfFTIR_BusName]  From QT_ARINC_BusDnld)

Getting query to complex error.

K
0
 
Rey Obrero (Capricorn1)Commented:
you said another table, now you are using a query ???

change that query to make table query and then use the created table.
0
 
Karen SchaeferAuthor Commented:
I still have an issue with my from clause.

DELETE TA_FTIR.*
FROM
(
SELECT TA_FTIR.FTIR_BusName
FROM TA_FTIR, TD_ARINC_BusDnld
WHERE (((TA_FTIR.FTIR_Type) Like "A429" Or (TA_FTIR.FTIR_Type)="A629" Or (TA_FTIR.FTIR_Type)="A664" Or (TA_FTIR.FTIR_Type)="HFCI" Or (TA_FTIR.FTIR_Type)="ENET" Or (TA_FTIR.FTIR_Type)="DAR"))
GROUP BY TA_FTIR.FTIR_BusName
HAVING (((TA_FTIR.FTIR_BusName) Not In (Select [FTIR_BusName]  From TD_ARINC_BusDnld ))) as A INNER JOIN TA_FTIR ON A.FTIR_BusName = TA_FTIR.FTIR_MeasNo;

Open in new window

0
 
awking00Commented:
delete from ta_ftir
where not exists
(select 1 from td_ftir
 where td_ftir.keyfield = ta.ftir.keyfield)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now