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