Home » SQL & PL/SQL » SQL & PL/SQL » Compare two tables
Compare two tables [message #275361] Fri, 19 October 2007 11:41 Go to next message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
I need to compare two tables and insert rows from the first table when the rows doesn't exist on second table.

I have procedure that does it but for some reason, the comparing of float values gives me duplicates. So I tried to eliminate duplicates by deleting those after the insert. This doesn't work for large number of rows(I have 5000 rows with 25 columns and around 10 of them are float columns and I ended up gettting more 1 million rows). So I used a simpler query of

Insert into table 2(select * from Table1 minus select * from table2)

It inserted 199 rows when Table 2 is empty but table1 returns 200 rows. There are no duplicates on table1 either...

Can anyone let me know why it is not inserting the one row?
Re: Compare two tables [message #275363 is a reply to message #275361] Fri, 19 October 2007 11:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What query did you use to check for duplicates on table 1?
Re: Compare two tables [message #275364 is a reply to message #275361] Fri, 19 October 2007 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone let me know why it is not inserting the one row?
The computer gremlin stole the missing row.
Alternatively you are not accurately reporting reality.
When things do not appear to be as they should, they probably aren't as they should be.
Re: Compare two tables [message #275367 is a reply to message #275361] Fri, 19 October 2007 12:08 Go to previous messageGo to next message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
This is what I use to see whether there are any duplicates

SELECT Col1, Col2... FROM TABLE1
WHERE rowid not in
(SELECT MIN(rowid) FROM TABLE1
GROUP BY Col1,Col2..)

[Updated on: Fri, 19 October 2007 12:50]

Report message to a moderator

Re: Compare two tables [message #275375 is a reply to message #275367] Fri, 19 October 2007 12:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
This can't be what you use, because that would result in an

ORA-01747: invalid user.table.column, table.column, or column specification
Re: Compare two tables [message #275376 is a reply to message #275367] Fri, 19 October 2007 12:43 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
That will give you X-1 rows returned (based on the "concept" I see, since the syntax would surely return an error)
Re: Compare two tables [message #275377 is a reply to message #275375] Fri, 19 October 2007 12:44 Go to previous messageGo to next message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
It is a valid one. I can run it with no errors.
Re: Compare two tables [message #275379 is a reply to message #275361] Fri, 19 October 2007 12:55 Go to previous message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
Thanks, I figured it out. It just that I was looking at the results wrong window in Toad. Sorry for the trouble.
Previous Topic: How to merge data from two temp tables in 'with' clause
Next Topic: Oracle 10G Outer join problem. Need help asap
Goto Forum:
  


Current Time: Tue Dec 06 14:00:04 CST 2016

Total time taken to generate the page: 0.27055 seconds