Обсуждение: Multi-row UPDATE using value from other table?

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.

On Sunday 15 May 2005 6:55 am, Taisuke Yamada wrote:
> 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);

I don't know if it can be done in a single statement but for the
update part try something like this. (I haven't tried it so it may
need some tweaking - you will especially need to decide what to do if
the original value is null and what to do the value for a particular
id in bar is null. You might need to use case statements to get what
you want):
update foo set value = value + coalesce(select bar.value from bar
where bar.id = foo.id),0);

To add the missing values you can use:
insert into foo (id,value) (select bar.id,bar.value from bar where not
exists (select 1 from foo where foo.id=bar.id));

Wrap those two into a transaction and it should do what you want.

Just one possible solution - there are probably better ones that I
can't quite see the morning after all that wedding champaigne. :)

Cheers,
Steve