Search Postgresql Archives

Multi-row UPDATE using value from other table?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi. I'm trying to come up with SQL that updates value of one table
by adding SELECTed values from another table.

Say I have two tables:

  CREATE TABLE foo (id INT NOT NULL PRIMARY KEY, value INT);
  CREATE TABLE bar (id INT NOT NULL PRIMARY KEY, value INT);

What I want to do is to take values from one table ("foo"), and
add (or just insert) that to column in another table ("bar").

Using example with MySQL, I can express it as follows:

  -- Do an INSERT-or-REPLACE operation
  REPLACE INTO foo (id, value)
  -- Compute sum and let it "REPLACE" existing entries
  SELECT foo.id, foo.value + bar.value FROM foo, bar where foo.id = bar.id
  UNION
  -- These are new entries and so will simply be "INSERT"ed.
  SELECT id, value FROM bar WHERE id NOT IN (SELECT id FROM foo);

Now, I know "REPLACE" is nonstandard and PostgreSQL won't support it.
However, I'd like to know if there's efficient way to do multi-row
UPDATE using data from other table. With above example, everything is
done inside database. If I use more standard "SELECT and UPDATE",
I'd have to call UPDATE statement N times from program, where N depends
on result of first SELECT.

What would be the best way to do it? I don't mind being PostgreSQL-
specific, so maybe something like

  1. CREATE temporary table
  2. INSERT new result into temporary table
  3. DELETE matching entries from destination table
  4. INSERT values to destination by SELECTing from temporary table

is the way to go?

Best Regards,
--
Taisuke Yamada <tyamadajp@xxxxxxxxxxxxxxxxx>
2268 E9A2 D4F9 014E F11D  1DF7 DCA3 83BC 78E5 CD3A

Message to my public address may not be handled in a timely manner.
For a direct contact, please use my private address on my namecard.

Attachment: signature.asc
Description: OpenPGP digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux