Multi-row UPDATE using value from other table? - Mailing list pgsql-general

From Taisuke Yamada
Subject Multi-row UPDATE using value from other table?
Date
Msg-id 428754D3.9030904@list.rakugaki.org
Whole thread Raw
Responses Re: Multi-row UPDATE using value from other table?  (Steve Crawford <scrawford@pinpointresearch.com>)
List pgsql-general
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@spam.rakugaki.org>
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

pgsql-general by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: function bit(integer)
Next
From: Steve Crawford
Date:
Subject: Re: Multi-row UPDATE using value from other table?