Re: Multi-row UPDATE using value from other table?

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