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
