pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcomparing 2 tables. . .
14+ messages / 9 participants
[nested] [flat]
* comparing 2 tables. . .
@ 1999-09-24 13:22 JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
1999-09-24 14:32 ` Re: [SQL] comparing 2 tables. . . Eric BASIER <basier@ipgp.jussieu.fr>
0 siblings, 1 reply; 14+ messages in thread
From: JT Kirkpatrick @ 1999-09-24 13:22 UTC (permalink / raw)
To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] comparing 2 tables. . .
1999-09-24 13:22 comparing 2 tables. . . JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
@ 1999-09-24 14:32 ` Eric BASIER <basier@ipgp.jussieu.fr>
0 siblings, 0 replies; 14+ messages in thread
From: Eric BASIER @ 1999-09-24 14:32 UTC (permalink / raw)
To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; PGSQL SQL <pgsql-sql@hub.org>
Hello,
You can try this, (but i think it may take a long time )
1 : Find which tuples are in t1 and not in t2
select distinct t1.pk from t1 where t1.pk not in (select t2.pk from t2);
2 : Find which tuples are in t2 and not in t1
select distinct t2.pk from t2 where t2.pk not in (select t1.pk from t1);
In the case that you only want to known the references
If you want to rebuilt your table after analyze you can make this :
select distinct t1.pk into t1b from t1 where t1.pk not in (select t2.pk
from t2);
select distinct t2.pk into t2b from t2 where t2.pk not in (select t1.pk
from t1);
After your analyze you can insert tuples of table t1b into table t1 and
tuples of table t2b into table t2.
Regards
--
Basier Eric e-mail : basier@ipgp.jussieu.fr
http://geoscope.ipgp.jussieu.fr
IPGP Observatoires Sismologie-Volcanologie
4,Place Jussieu 75005 Paris Tour 24-14 4eme Etage Tel 01 44 27 38 96
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] comparing 2 tables. . .
@ 1999-09-24 13:50 Daniel Péder <dpeder@infoset.cz>
1999-09-24 14:32 ` RE: [SQL] comparing 2 tables. . . Stuart Rison <rison@biochemistry.ucl.ac.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Péder @ 1999-09-24 13:50 UTC (permalink / raw)
To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
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 );
--
dan peder
dpeder@infoset.cz
http://shop.culture.cz
-----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
************
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] comparing 2 tables. . .
1999-09-24 13:50 RE: [SQL] comparing 2 tables. . . Daniel Péder <dpeder@infoset.cz>
@ 1999-09-24 14:32 ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-09-24 20:47 ` Re: [SQL] comparing 2 tables. . . Albert REINER <areiner@tph.tuwien.ac.at>
0 siblings, 1 reply; 14+ messages in thread
From: Stuart Rison @ 1999-09-24 14:32 UTC (permalink / raw)
To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>; +Cc: jt-kirkpatrick@mpsllc.com
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] comparing 2 tables. . .
1999-09-24 13:50 RE: [SQL] comparing 2 tables. . . Daniel Péder <dpeder@infoset.cz>
1999-09-24 14:32 ` RE: [SQL] comparing 2 tables. . . Stuart Rison <rison@biochemistry.ucl.ac.uk>
@ 1999-09-24 20:47 ` Albert REINER <areiner@tph.tuwien.ac.at>
1999-09-25 13:35 ` Re: [SQL] comparing 2 tables. . . Engard Ferenc <fery@pons.sote.hu>
1999-09-27 10:59 ` Re: [SQL] comparing 2 tables. . . Stuart Rison <rison@biochemistry.ucl.ac.uk>
0 siblings, 2 replies; 14+ messages in thread
From: Albert REINER @ 1999-09-24 20:47 UTC (permalink / raw)
To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
On Fri, Sep 24, 1999 at 03:32:51PM +0100, Stuart Rison wrote:
...
> 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);
Maybe this is trivial, but...
Is there a simple way to see that/why the last query is faster?
Albert.
--
---------------------------------------------------------------------------
Post an / Mail to / Skribu al: Albert Reiner <areiner@tph.tuwien.ac.at>
---------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] comparing 2 tables. . .
1999-09-24 13:50 RE: [SQL] comparing 2 tables. . . Daniel Péder <dpeder@infoset.cz>
1999-09-24 14:32 ` RE: [SQL] comparing 2 tables. . . Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-09-24 20:47 ` Re: [SQL] comparing 2 tables. . . Albert REINER <areiner@tph.tuwien.ac.at>
@ 1999-09-25 13:35 ` Engard Ferenc <fery@pons.sote.hu>
1 sibling, 0 replies; 14+ messages in thread
From: Engard Ferenc @ 1999-09-25 13:35 UTC (permalink / raw)
To: pgsql-sql
On Fri, 24 Sep 1999, Albert REINER wrote:
>On Fri, Sep 24, 1999 at 03:32:51PM +0100, Stuart Rison wrote:
>...
>> 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);
>
>Maybe this is trivial, but...
>
>Is there a simple way to see that/why the last query is faster?
Yes, the simple way is to look at the thread 'subselect performance'
a few days ago... :-)) In essence, I don't know... ;) Something
about that the subselect's result always recomputed for each row in
the main select or what, if I got the idea. (And in the 2nd case the
subselect has only one row, provided that the primary keys are
unique.)
Circum
PS: Anyway, as I can figure it out, this is that 'EXISTS' workaround
that you mentioned in that thread, right?
__ @
/ \ _ _ Engárd Ferenc
l | ( \ / | | (\/) mailto:s-fery@kkt.sote.hu
\__/ | | \_ \_/ I I http://pons.sote.hu/~s-fery
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] comparing 2 tables. . .
1999-09-24 13:50 RE: [SQL] comparing 2 tables. . . Daniel Péder <dpeder@infoset.cz>
1999-09-24 14:32 ` RE: [SQL] comparing 2 tables. . . Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-09-24 20:47 ` Re: [SQL] comparing 2 tables. . . Albert REINER <areiner@tph.tuwien.ac.at>
@ 1999-09-27 10:59 ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
1 sibling, 0 replies; 14+ messages in thread
From: Stuart Rison @ 1999-09-27 10:59 UTC (permalink / raw)
To: Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
Well, I guess officially you're supposed to be able to decode an EXPLAIN
of each statement but I'm not quite that savvy (yet).
With regards to this specific examples, I seem to remember a number of
previous postings that say that (NOT) EXISTS type statements are much
faster that (NOT) IN (SELECT...) type statements (because that kind of
sub-select is currently sub-optimally implemented in PG).
regards,
Stuart.
On Fri, 24 Sep 1999, Albert REINER wrote:
> On Fri, Sep 24, 1999 at 03:32:51PM +0100, Stuart Rison wrote:
> ...
> > 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);
>
> Maybe this is trivial, but...
>
> Is there a simple way to see that/why the last query is faster?
>
> Albert.
>
> --
>
> ---------------------------------------------------------------------------
> Post an / Mail to / Skribu al: Albert Reiner <areiner@tph.tuwien.ac.at>
> ---------------------------------------------------------------------------
>
> ************
>
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] comparing 2 tables. . .
@ 1999-09-24 14:06 Daniel Péder <dpeder@infoset.cz>
0 siblings, 0 replies; 14+ messages in thread
From: Daniel Péder @ 1999-09-24 14:06 UTC (permalink / raw)
To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
OOPS, i've made mistake turning the key and tab name... :-((
(the "from tab1" could be missed, however, put it there for better readability... )
this should be ok:
select tab1.pkey from tab1 where tab1.pkey not in ( tab2.pkey );
--
dan peder
dpeder@infoset.cz
http://shop.culture.cz
-----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
************
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] comparing 2 tables. . .
@ 1999-09-25 04:04 omid omoomi <oomoomi@hotmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: omid omoomi @ 1999-09-25 04:04 UTC (permalink / raw)
To: rison@biochemistry.ucl.ac.uk; +Cc: pgsql-sql@hub.org
If you want to use 'minus'(just like oracle), in pg you should use 'except'
. ie:
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] comparing 2 tables. . .
@ 1999-09-26 04:02 omid omoomi <oomoomi@hotmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: omid omoomi @ 1999-09-26 04:02 UTC (permalink / raw)
To: jt-kirkpatrick@mpsllc.com; +Cc: pgsql-sql@hub.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* comparing 2 tables
@ 2005-03-15 09:36 U K Laxmi <laxmiuk@yahoo.com>
2005-03-15 09:52 ` Re: comparing 2 tables Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 14+ messages in thread
From: U K Laxmi @ 2005-03-15 09:36 UTC (permalink / raw)
To: laxmiuk@yahoo.com; +Cc: pgsql-sql
I've two Ms Access tables with the same structure.
Tablestructure is as follows:
Table table1
SbPartNo char(50)
PartDesc char(200)
manPartNo char(200)
manufacturer char(100)
vendor char(100)
refDesi char(200)
qty char(5)
My requirement is as follows:
table 1
1111 partDesc1 111111 xyz1 vendor1 refdesi1 2
2222 partDesc2 222222 xyz2 vendor2 refdesi2 4
3333 partDesc3 333333 xyz3 vendor3 refdesi3 6
table 2
2222 partDesc2 222222 xyz2 vendor2 refdesi2 4
3333 partDesc3 343434 xyz3 vendor3 refdesi3 6
4444 partDesc4 444444 xyz4 vendor4 refDesi4 8
Table 3 structure is:
oldSbPartNo char(50)
oldPartDesc char(200)
oldmanPartNo char(200)
oldmanufacturer char(100)
oldvendor char(100)
oldrefDesi char(200)
oldqty char(5)
newSbPartNo char(50)
newPartDesc char(200)
newmanPartNo char(200)
newmanufacturer char(100)
newvendor char(100)
newrefDesi char(200)
newqty char(5)
After comparing the above 2 tables, i want the result
to be stored in third table as follows.
1111 partDesc1 111111 xyz1 vendor1 refdesi1 2 null
null null null null null null
3333 partDesc3 333333 xyz3 vendor3 refdesi3 6 3333
partDesc3 343434 xyz3 vendor3 refdesi3 6
null null null null null null null 4444 partDesc4
444444 xyz4 vendor4 refDesi4 8
Here what we should observe is, 1st row in table 1 not
present in table2. 2nd row in table1 is modified in
table2. 3rd row in table 2 is not present in table1.
So, after comparing table 1 and table2, the
differences are captured as old and new. Old referes
to table1 data and new referes to table2 data. The
rows that are equal in both table 1 and table 2 should
not be captured in table 3.
Is itpossible to do this in Ms Access? If so, how.
I appreciate if anyone can provide me the sql syntax
for it. I'm using this for a web application. So, pure
SQL is needed.
__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: comparing 2 tables
2005-03-15 09:36 comparing 2 tables U K Laxmi <laxmiuk@yahoo.com>
@ 2005-03-15 09:52 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 14+ messages in thread
From: Richard Huxton @ 2005-03-15 09:52 UTC (permalink / raw)
To: U K Laxmi <laxmiuk@yahoo.com>; +Cc: pgsql-sql
U K Laxmi wrote:
> I've two Ms Access tables with the same structure.
This is a PostgreSQL list - you'd probably be better using a
Microsoft-Access list.
[snip]
> Here what we should observe is, 1st row in table 1 not
> present in table2. 2nd row in table1 is modified in
> table2. 3rd row in table 2 is not present in table1.
> So, after comparing table 1 and table2, the
> differences are captured as old and new. Old referes
> to table1 data and new referes to table2 data. The
> rows that are equal in both table 1 and table 2 should
> not be captured in table 3.
>
> Is itpossible to do this in Ms Access? If so, how.
In general terms, with MS-Access, I'd build separate queries for each
step. So you'd want a query for:
1. Reset table3
2. Insert to table3 rows in table1 and not in table2
3. Insert to table3 rows in table2 and not in table1
...etc...
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: comparing 2 tables
@ 2005-03-15 10:10 U K Laxmi <laxmiuk@yahoo.com>
2005-03-15 10:36 ` Re: comparing 2 tables Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 14+ messages in thread
From: U K Laxmi @ 2005-03-15 10:10 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Thank you for the reply. Sorry for posting this
problem here. Thought SQL syntax for Ms Access and
PostgreSQL are similar. So, i posted here.
My problem is:
I've original version of a table called PMM (Product
Material Master). Thro' a web interface, user can
change that table contents. Once changed i need to
raise a ECN (Engineering CHange Note) specifying what
changes happened to original PMM table whether rows
are deleted, new rows are added or existing rows are
modified etc. I've both old and new version of PMM
tables.
The difference between two PMM tables are captured in
a third table called ECN and it has both original &
new PMM table entries which are not same.
Table table1
SbPartNo char(50)
PartDesc char(200)
manPartNo char(200)
manufacturer char(100)
vendor char(100)
refDesi char(200)
qty char(5)
My requirement is as follows:
table 1
1111 partDesc1 111111 xyz1 vendor1 refdesi1 2
2222 partDesc2 222222 xyz2 vendor2 refdesi2 4
3333 partDesc3 333333 xyz3 vendor3 refdesi3 6
table 2
2222 partDesc2 222222 xyz2 vendor2 refdesi2 4
3333 partDesc3 343434 xyz3 vendor3 refdesi3 6
4444 partDesc4 444444 xyz4 vendor4 refDesi4 8
Table 3 structure is:
oldSbPartNo char(50)
oldPartDesc char(200)
oldmanPartNo char(200)
oldmanufacturer char(100)
oldvendor char(100)
oldrefDesi char(200)
oldqty char(5)
newSbPartNo char(50)
newPartDesc char(200)
newmanPartNo char(200)
newmanufacturer char(100)
newvendor char(100)
newrefDesi char(200)
newqty char(5)
After comparing the above 2 tables, i want the result
to be stored in third table as follows.
1111 partDesc1 111111 xyz1 vendor1 refdesi1 2 null
null null null null null null (null for new entries
b'cozthis row is deleted in new version ofPMM table)
3333 partDesc3 333333 xyz3 vendor3 refdesi3 6 3333
partDesc3 343434 xyz3 vendor3 refdesi3 6 (this row has
entries for both old & new fields b'coz this row is
modified from original one)
null null null null null null null 4444 partDesc4
444444 xyz4 vendor4 refDesi4 8 (this row has old
entries as null, bcoz this is a new row of data that
is being added to original PMM table)
I already posted this issue to 3 of the free web
forums for MS Access. No reply yet. So, thought i will
post here. If it's not acceptible, i will stop this
issue in this list.
If somebody can give some insights to this problem, it
will be a great help.
I won't mind implementing this logic in multiple
queries.
Thank you so much.
--- Richard Huxton <dev@archonet.com> wrote:
> U K Laxmi wrote:
> > I've two Ms Access tables with the same structure.
>
> This is a PostgreSQL list - you'd probably be better
> using a
> Microsoft-Access list.
>
> [snip]
> > Here what we should observe is, 1st row in table 1
> not
> > present in table2. 2nd row in table1 is modified
> in
> > table2. 3rd row in table 2 is not present in
> table1.
> > So, after comparing table 1 and table2, the
> > differences are captured as old and new. Old
> referes
> > to table1 data and new referes to table2 data. The
> > rows that are equal in both table 1 and table 2
> should
> > not be captured in table 3.
> >
> > Is itpossible to do this in Ms Access? If so, how.
>
> In general terms, with MS-Access, I'd build separate
> queries for each
> step. So you'd want a query for:
> 1. Reset table3
> 2. Insert to table3 rows in table1 and not in table2
> 3. Insert to table3 rows in table2 and not in table1
> ...etc...
>
> HTH
> --
> Richard Huxton
> Archonet Ltd
>
__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: comparing 2 tables
2005-03-15 10:10 Re: comparing 2 tables U K Laxmi <laxmiuk@yahoo.com>
@ 2005-03-15 10:36 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 14+ messages in thread
From: Richard Huxton @ 2005-03-15 10:36 UTC (permalink / raw)
To: U K Laxmi <laxmiuk@yahoo.com>; +Cc: pgsql-sql
U K Laxmi wrote:
> Thank you for the reply. Sorry for posting this
> problem here. Thought SQL syntax for Ms Access and
> PostgreSQL are similar. So, i posted here.
>
> My problem is:
>
> I've original version of a table called PMM (Product
> Material Master). Thro' a web interface, user can
> change that table contents. Once changed i need to
> raise a ECN (Engineering CHange Note) specifying what
> changes happened to original PMM table whether rows
> are deleted, new rows are added or existing rows are
> modified etc. I've both old and new version of PMM
> tables.
>
> The difference between two PMM tables are captured in
> a third table called ECN and it has both original &
> new PMM table entries which are not same.
Another option I've used in similar cases is to add a "version" column
to the relevant tables (PMM in your case).
Users can only change rows with version="editing" (or "live" or other
code) and after review can confirm their changes. This makes a copy of
all the data but with a new version-code (usually auto-generated). You
can now compare any two versions to track changes.
This system works well if you have relatively infrequent changes in
large batches. In my case it was company-profile data (services,
specialisations, contact personnel etc) and users would update their
data at most every few months. I actually had a review phase in my
system between editing and publishing a new version of a company's data.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2005-03-15 10:36 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-24 13:22 comparing 2 tables. . . JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
1999-09-24 14:32 ` Eric BASIER <basier@ipgp.jussieu.fr>
1999-09-24 13:50 RE: [SQL] comparing 2 tables. . . Daniel Péder <dpeder@infoset.cz>
1999-09-24 14:32 ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-09-24 20:47 ` Albert REINER <areiner@tph.tuwien.ac.at>
1999-09-25 13:35 ` Engard Ferenc <fery@pons.sote.hu>
1999-09-27 10:59 ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-09-24 14:06 RE: [SQL] comparing 2 tables. . . Daniel Péder <dpeder@infoset.cz>
1999-09-25 04:04 RE: [SQL] comparing 2 tables. . . omid omoomi <oomoomi@hotmail.com>
1999-09-26 04:02 RE: [SQL] comparing 2 tables. . . omid omoomi <oomoomi@hotmail.com>
2005-03-15 09:36 comparing 2 tables U K Laxmi <laxmiuk@yahoo.com>
2005-03-15 09:52 ` Re: comparing 2 tables Richard Huxton <dev@archonet.com>
2005-03-15 10:10 Re: comparing 2 tables U K Laxmi <laxmiuk@yahoo.com>
2005-03-15 10:36 ` Re: comparing 2 tables Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox