pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Updating one table with data from another
2+ messages / 2 participants
[nested] [flat]

* Updating one table with data from another
@ 2009-08-18 21:02  drew <iamdrewhayes@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: drew @ 2009-08-18 21:02 UTC (permalink / raw)
  To: pgsql-sql

Hey all,
There are two things I need to do:
1. Update existing rows with new data
2. Append new rows

I need to update only some of the fields table1 with data from
table2.  These tables have the exact same fields.

So here's what I have currently for appending new rows (rows where CID
does not currently exist in table1, but have been added to table2):
INSERT INTO table1 (field1, field2, ...)
SELECT field1, field2, ...
FROM table2
WHERE NOT EXISTS (SELECT CID FROM table1);


But something is wrong with the logic there and I'm not quite getting
it.


For the update part, here's what I have:
UPDATE table1
SET field1 = table2.field1
field2 = table2.field2,
...,
FROM table1 INNER JOIN table2 ON table1.CID=table2.CID;

I'm not sure what's wrong with this one either.
Any help would be greatly appreciated!



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Updating one table with data from another
@ 2009-08-20 10:51  Jasen Betts <jasen@xnet.co.nz>
  parent: drew <iamdrewhayes@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Jasen Betts @ 2009-08-20 10:51 UTC (permalink / raw)
  To: pgsql-sql

On 2009-08-18, drew <iamdrewhayes@gmail.com> wrote:
> Hey all,
> There are two things I need to do:
> 1. Update existing rows with new data
> 2. Append new rows
>
> I need to update only some of the fields table1 with data from
> table2.  These tables have the exact same fields.
>
> So here's what I have currently for appending new rows (rows where CID
> does not currently exist in table1, but have been added to table2):
> INSERT INTO table1 (field1, field2, ...)
> SELECT field1, field2, ...
> FROM table2
> WHERE NOT EXISTS (SELECT CID FROM table1);
>
>
> But something is wrong with the logic there and I'm not quite getting
> it.

the where clause is wrong.


WHERE NOT EXISTS (SELECT 1 FROM table1 where table1.cid=table2.cid);

or 

WHERE NOT cid IN (SELECT CID FROM table1);





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2009-08-20 10:51 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-18 21:02 Updating one table with data from another drew <iamdrewhayes@gmail.com>
2009-08-20 10:51 ` Jasen Betts <jasen@xnet.co.nz>

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