RE: [SQL] comparing 2 tables. . .

Use "EXCEPT" instead of "MINUS" :
select * from tab1
except
select * from tab2;

regards. omid.


>From: Stuart Rison <rison@biochemistry.ucl.ac.uk>
>To: "'pgsql-sql@hub.org'" <pgsql-sql@hub.org>
>CC: jt-kirkpatrick@mpsllc.com
>Subject: RE: [SQL] comparing 2 tables. . .
>Date: Fri, 24 Sep 1999 15:32:51 +0100 (BST)
>
>On Fri, 24 Sep 1999, [iso-8859-2] Daniel P�der wrote:
>
> > try this, it should select what in tab1 is not in tab2 ( compared by 
>pkeys )
> >
> > select pkey.tab1 where pkey.tab1 not in ( pkey.tab2 );
>
>erm... unless the 'pkey' is an operator of some kind in PG 6.5.x that I'm
>completely unaware of, I don't think that will work.
>
>Try:
>
>SELECT tab1.pkey FROM <your_table> tab1 WHERE tab1.pkey NOT IN (SELECT
>tab2.pkey FROM <your_other_table> tab2.pkey);
>
>or else (faster):
>
>SELECT tab1.pkey FROM <your_table> tab1 WHERE NOT EXISTS (SELECT 1 FROM
><your_other_table> tab2 WHERE tab1.pkey=tab2.pkey);
>
>Finally, if you have version 6.5.x you could use MINUS:
>
>SELECT tab1.pkey FROM <your_table> tab1
>MINUS
>SELECT tab2.pkey FROM <your_other_table> tab2;
>
>Can't vouch for the last one as I only have PG 6.4.2.
>
>HTH,
>
>S.
>
>
> >
> > -----Original Message-----
> > From:    JT Kirkpatrick [SMTP:jt-kirkpatrick@mpsllc.com]
> >
> > can anyone help please??
> >
> > we have two tables in our database that *should* have the exact same 
>number
> > of records, with identical values in their corresponding primary keys 
>(pk
> > same in both tables, int4).  for this example, table t1 with primary key
> > pk, and table t2 with primary key pk.  after about 3 months of use and a
> > crash last night i see that the two tables do NOT have the same number 
>of
> > records -- one is missing some that it should have.  is there a way i 
>can
> > compare the two tables and select ONLY the records that do NOT exist in 
>the
> > 2nd table??  if i could identify the records (and the pk) then i could
> > manually insert them.  each table has over 50,000 records so it is not
> > feasable to review each record to find them manually (which i tried last
> > night. . .).
> >
> > we are struggling this morning -- any help you could provide quickly 
>would
> > be greatly appreciated!
> >
> > jt kirkpatrick / mps
>
>Stuart C. G. Rison
>Department of Biochemistry and Molecular Biology
>6th floor, Darwin Building, University College London (UCL)
>Gower Street, London, WC1E 6BT, United Kingdom
>Tel. 0207 504 2303, Fax. 0207 380 7033
>e-mail: rison@biochem.ucl.ac.uk
>
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com