CODEWITHSUNDEEP
sqloracle-databaseoracle11gsubquery
user3162204
user3162204

Reputation: 11

oracle subquery : error not getting resolved

I have two tables:

one is ATM and other is HOST(structure is same) There is data in ATM which isn't present in HOST. I want to sync the tables, i.e., whatever data is missing from HOST and present in ATM , must be returned.

My solution:

SELECT HOST.TRANS_ID WHERE NOT EXISTS 
   (SELECT * FROM ATM WHERE ATM.TRANS_ID = HOST.TRANS_ID)

still i am getting an error!

Upvotes: 1

Views: 48

Answers (1)

Ollie
Ollie

Reputation: 17538

Firstly, to address the error you are getting, your SQL is invalid. To perform the query you are trying to write you would need a FROM clause in your outer query, it should read:

SELECT HOST.TRANS_ID 
  FROM host
 WHERE NOT EXISTS 
      (SELECT * 
         FROM ATM 
        WHERE ATM.TRANS_ID = HOST.TRANS_ID);

However, your query will not return the data you need as by your definition ("data is missing from host"), if the data does not exist in HOST, how can you select it?

I would be inclined to use the following query to solve your issue:

SELECT trans_id
  FROM atm
  LEFT OUTER JOIN host
 USING trans_id
  WHERE host.rowid IS NULL;

In this query, we perform an outer join returning all rows from ATM (as it hold rows not present in HOST). The rows in HOST that are not present will return a NULL rowid so by selecting all TRANS_ID values where the HOST rowid is null will show you the ATM values that have no corresponding HOST value which I believe is what you are wanting.

HTH

Upvotes: 1

Related Questions