Comparing two tables, update some then insert the rest

Comparing two tables, update some then insert the rest

Post by DC » Sun, 09 Dec 2001 08:30:15



I have two tables.
"oldtable" and "newtable"
If the street_address and zip are the same
I want to update "oldtable" with all the data
from "newtable" except for the primary_id.
I also want to insert all other "newtable" rows to "oldtable"

Sorry I'm new to postgresql.
I read the documentation and found only simple selects and inserts.

 
 
 

Comparing two tables, update some then insert the rest

Post by Gary Strad » Thu, 13 Dec 2001 10:57:11


DC,

To insert all other "newtable" rows to "oldtable" rows you could do:

insert newtable
select * from oldtable
where primary_id not in (select primary_id from newtable)

Assuming that the ID's are synced up between the tables.  If they're
sequence fields they probably won't be.

Or:
insert newtable
select * from oldtable a
where not exists (select 1 from newtable b where a.primary_id =
b.primary_id)

If the primary_id fields won't match by definition, you could replace
the "a.primary_id = b.primary_id" to match on other fields (as long as
they uniquely identify the row).  The purpose of that clause is to not
copy across records that are the same between the two tables.

Hope that helps.
Gary


> I have two tables.
> "oldtable" and "newtable"
> If the street_address and zip are the same
> I want to update "oldtable" with all the data
> from "newtable" except for the primary_id.
> I also want to insert all other "newtable" rows to "oldtable"

> Sorry I'm new to postgresql.
> I read the documentation and found only simple selects and inserts.


 
 
 

Comparing two tables, update some then insert the rest

Post by David Brya » Sun, 16 Dec 2001 12:06:11



> DC,

> To insert all other "newtable" rows to "oldtable" rows you could do:

> insert newtable
> select * from oldtable
> where primary_id not in (select primary_id from newtable)

> Assuming that the ID's are synced up between the tables.  If they're
> sequence fields they probably won't be.

> Or:
> insert newtable
> select * from oldtable a
> where not exists (select 1 from newtable b where a.primary_id =
> b.primary_id)

> If the primary_id fields won't match by definition, you could replace
> the "a.primary_id = b.primary_id" to match on other fields (as long as
> they uniquely identify the row).  The purpose of that clause is to not
> copy across records that are the same between the two tables.

> Hope that helps.
> Gary



>> I have two tables.
>> "oldtable" and "newtable"
>> If the street_address and zip are the same
>> I want to update "oldtable" with all the data
>> from "newtable" except for the primary_id.
>> I also want to insert all other "newtable" rows to "oldtable"

>> Sorry I'm new to postgresql.
>> I read the documentation and found only simple selects and inserts.

Gary is pretty much right here. Create 2 views in Postgres. One that lists
all the data from newtable that matches in both tables, and another that
lists all the data in the newtable that doesn't match.

Then simply run an update query on the first view, and an append query on
the second.

If your having a tough time with this I'd recommend doing this through MS
Access. While some might want to flame me for saying that, MS Access does
have some nice capabilities that makes it an excellent data manipulation
tool. Of course that's going to take two computers. :-)

--
David Bryan
Visual Programming Services

 
 
 

1. Insert in 2 tables - Second insert fails then first, then second, then first...

Hi there,

I'm currently trying to run two Insert queries, one after the other
into a MySQL database via DBI. The first inserts into table poheader,
the second into table pocontent

What's happening, though, is that I'm getting consecutive successes
and failures with each attempt.

As in, if I get a successful insert into POHeader, POContent remains
empty, and vice versa. Even though both queries return a rowsaffected
value of 1.

Initially, I had them set up as simply

$query1 = QueryCode1;
$query2 = QueryCode2;

with consecutive do statements,

$dbh->do($query1);
$dbh->do($query2);

However, I thought perhaps there was something in the concurrent
connection causing the problem so I split it up into two separate
connections and prepare and execute methods but, still to no avail.

The two tables in question are in a one to many relationship with a
code from one being inserted in the other but I'm inserting the record
for the 1 side of the relationship first, so that shouldn't be an
issue, and, anyway, the second query is executing without a relevant
key being in the parent table.

Some code for those who wish to get a clearer picture

my $insertheader = qq{INSERT INTO poheader (AutoID, ...) VALUES
(NULL,$...)};

my $insertcontent = qq{INSERT INTO pocontent (AutoID, ...) VALUES
(NULL, ...)};

my $poheadrows = $dbh->prepare ($insertheader);
$poheadrows->execute() or warn "Warning: Could not insert data into PO
Header table. \n\n";

# Now close the connection

$dbh->disconnect;

my $dbh2 = DBI->connect ($dsn, $user_name, $password);

my $sth = $dbh2->prepare($insertcontent);

$sth->execute() or warn "Warning: Could not insert data into PO
Content table. \n\n";

$dbh2->disconnect;

Anyone have any ideas why this might be happening? Is there some cache
I have to flush - it's as if it's only ever inserting the last one
that didn't get inserted. Would really appreciate any pointers.

Thanks,
Vincent

2. FrontPage 98 Beta Host Accounts

3. Comparing two (largish) tables on different servers

4. problem with emacs 19.34b on linux in vts

5. Insert into two tables

6. Senior Business Analyst

7. <FS: Killer Keys for 01W; 50% Off>

8. unable to insert data into two tables at same time

9. Passing table names to PL/PGSQL for SELECT/UPDATE/INSERT

10. UPDATE/INSERT on multiple co-dependent tables

11. Returning PK of first insert for second insert use.