Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do you compare one table to another to determine differences?

Re: How do you compare one table to another to determine differences?

From: Niall McPhillips <nmcp[nospam>
Date: Wed, 18 Nov 1998 11:34:07 +0100
Message-ID: <MPG.10bcc109ca278782989683@news.iprolink.ch>


[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



Niall McPhillips
Stephenson and Associates
chemin des Coquelicots 20
1214 Vernier-Geneva
Switzerland.
Tel: 	+41 (22) 341.5001
Fax: 	+41 (22) 341.5004

e-mail: nmcp[nospam]@s-and-a.com
Web: http://www.s-and-a.com Received on Wed Nov 18 1998 - 04:34:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US