?
Solved

query problem _ delete unmatch items from table

Posted on 2013-01-09
5
Medium Priority
?
204 Views
Last Modified: 2013-02-26
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
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 38761074
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
 

Author Comment

by:Karen Schaefer
ID: 38761101
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38761103
you said another table, now you are using a query ???

change that query to make table query and then use the created table.
0
 

Author Comment

by:Karen Schaefer
ID: 38761181
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 total points
ID: 38764025
delete from ta_ftir
where not exists
(select 1 from td_ftir
 where td_ftir.keyfield = ta.ftir.keyfield)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question