Home » SQL & PL/SQL » SQL & PL/SQL » Compare two tables (Compare two tables)
Compare two tables [message #275752] Mon, 22 October 2007 10:28 Go to next message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
I have 2 tables that need to be compared. Table A and Table B
Table A is the source table and Table B is the target table. I need to compare these two tables and when there are rows present in Table A that is not in Table B, I need to insert those rows into Table B. Right now I use

SELECT col1,col2
FROM TABLE A
MINUS
SELECT col1,Col2
FROM TABLE B

The problem right now is I have 140 rows in Table A. 200 Rows in Table B. In those I have 10 rows that are present in Table A which is not present in table B. I expect the above query should return those 10 Rows which is not. Could you tell me what I'm doing wrong?

Table A gets refreshed periodically(could be rows inserted or deleted). Table B should include both deleted and inserted rows from Table A.


Thanks!

Re: Compare two tables [message #275758 is a reply to message #275752] Mon, 22 October 2007 10:32 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It is probably because the rows are not uniquely keyed. Set operations like MINUS, UNION etc automatically remove duplicates.
Re: Compare two tables [message #275776 is a reply to message #275752] Mon, 22 October 2007 11:31 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

could you please try this:

select * from table A
where field A not in select * from table B
Re: Compare two tables [message #275782 is a reply to message #275776] Mon, 22 October 2007 12:28 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sbenosa wrote on Mon, 22 October 2007 18:31

could you please try this:

select * from table A
where field A not in select * from table B

What do you mean with that?
This is syntactically incorrect.
Previous Topic: escape function
Next Topic: From SQL server
Goto Forum:
  


Current Time: Tue Dec 06 02:31:12 CST 2016

Total time taken to generate the page: 0.10959 seconds