sam2929
asked on
finding common records from two tables
I have two tables with same columns i have key based upon that key i want to see if i have any dups or common records for that two tables
how can i do that.
Thanks
how can i do that.
Thanks
forgot the join:
select t1.keycol1, t1.keycol2 from tab1 t1, tab2 t2
where t1.keycol1=t2.keycol1 and t1.keycol2=t2.keycol2;
select t1.keycol1, t1.keycol2 from tab1 t1, tab2 t2
where t1.keycol1=t2.keycol1 and t1.keycol2=t2.keycol2;
You can also try out EXISTS clause.
assuming you want data only from table1 that EXISTS in table2
select t1.* from tab1 t1
WHERE EXISTS
(select 1 from tab2 t2
where t1.col1=t2.col1
and t1.col2=t2.col2)
assuming you want data only from table1 that EXISTS in table2
select t1.* from tab1 t1
WHERE EXISTS
(select 1 from tab2 t2
where t1.col1=t2.col1
and t1.col2=t2.col2)
ASKER
Now i want to delete the dups how can i do that
Something like:
Delete from table1 where rowid in
(
Select t1.rowid
From ...whatever select you wanted to use
)
If you want to delete from table2, just switch the names.
Delete from table1 where rowid in
(
Select t1.rowid
From ...whatever select you wanted to use
)
If you want to delete from table2, just switch the names.
Oops.
That deletes all rows. If that isn't what you want, just let us know.
That deletes all rows. If that isn't what you want, just let us know.
ASKER
No i don't want to delete all rows
table a looks like
doc_num yr line
123 2010 1
234 2010 1
table b looks like
doc_num yr line
123 2010 1
345 2010 1
i want final result as
doc_num yr line
234 2010 1
table b looks like
doc_num yr line
123 2010 1
345 2010 1
table a looks like
doc_num yr line
123 2010 1
234 2010 1
table b looks like
doc_num yr line
123 2010 1
345 2010 1
i want final result as
doc_num yr line
234 2010 1
table b looks like
doc_num yr line
123 2010 1
345 2010 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select key_col1, key_col2 from table1
intersect
select key_col1, key_col2 from table2
/
You could join them as well but I figure intersect will be faster.