Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How do you compare one table to another to determine differences?
[This followup was posted to comp.databases.oracle.server and a copy was
sent to the cited author.]
Hi,
Maybe I'm underestimating what you are trying to do but it seems that the following two statements or some variation thereupon could do the job.
Assuming that the table structures are identical ....
Get all deleted customers (i.e. customers that existed yesterday but not today):
create or replace view deleted_customers as
select * from yesterdays_customers
minus
select * from todays_customers;
Get all new, inserted customers (i,e, customers that exist today but didn't yesterday):
create or replace view inserted_customers as
select * from todays_customers
minus
select * from yesterdays_customers;
Hope this helps.
Niall.
In article <f1142.15834$W6.3254929_at_newscene.newscene.com>,
ramdan_at_mailexcite.com says...
> I have one table, a snapshot table of customers that i get nightly. the info
> is copied to a regular table that i can insert and delete from. the problem
> It is a complex snapshot so i don't have the ability to get just the updates.
> How can I, in sql, compare this current night's table to the previous night's
> one so that i can mark with are new custmore and insert them to master table
> and mark the dropped ones to delte them
>
> (customers in the current but not older are new customers and should be
> inserted, customers in old but not current are old customers and should be
> deleted)
>
--
Note: remove [nospam] from e-mail address for correct address
Tel: +41 (22) 341.5001 Fax: +41 (22) 341.5004