From: U K Laxmi <laxmiuk@yahoo.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: comparing 2 tables
Date: Tue, 15 Mar 2005 02:10:57 -0800 (PST)
Message-ID: <20050315101058.91004.qmail@web20025.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: laxmiuk@yahoo.com, dev@archonet.com
Subject: Re: comparing 2 tables
In-Reply-To: <20050315101058.91004.qmail@web20025.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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