Thread: found a way to update a table with data from another one

Hi everybody
I've read a quite old posting about the impossibility of updating a
view.
e.g.

Let t1 (Id, balance) and t2 (id, amount) be two tables

DEFINE VIEW U AS SELECT balance, amount FROM t1, t2 WHERE t1.id=t2.id;
UPDATE U SET balance=balance+amount

without writing a rule

I found a simpler way to do the job I wanted done

UPDATE t1 SET  balance = balance+t2.amount WHERE EXISTS (SELECT *
FROM t2 WHERE t2.id = t1.id)

DOES WORK PROPERLY.

Patrick JACQUOT <patrick.jacquot@anpe.fr> writes:
> I found a simpler way to do the job I wanted done

> UPDATE t1 SET  balance = balance+t2.amount WHERE EXISTS (SELECT *
> FROM t2 WHERE t2.id = t1.id)

> DOES WORK PROPERLY.

Does it?  I don't think that this will do what you are expecting,
because t2 in the outer query is not the same table reference as t2 in
the inner query --- there's an implicit FROM t2 in the outer query.
What this'll actually do is perform an unrestricted join of *all* rows
in t2 to each row in t1 that meets the WHERE condition (ie, has some
matching row in t2).

Because of the visibility rules for updates, only one of the joined
pairs for each t1 row will actually get into the final result ---
but it's unlikely to be the one you want.  When I tried it, it seemed
the first row to be processed in the t2 table got added to all the
t1 rows that had matches:

regression=> select * from t1;
id|balance
--+-------1|    1002|   10003|      0
(3 rows)

regression=> select * from t2;
id|amount
--+------1|    442|    55
(2 rows)

regression=> UPDATE t1 SET  balance = balance+t2.amount WHERE EXISTS (SELECT *
regression-> FROM t2 WHERE t2.id = t1.id);
UPDATE 2
regression=> select * from t1;
id|balance
--+-------3|      01|    1442|   1044
(3 rows)


I suspect the effect you really want is much simpler:

UPDATE t1 SET  balance = balance+amount FROM t2 WHERE t1.id = t2.id;

That gives me

regression=> select * from t1;
id|balance
--+-------3|      01|    1882|   1099
(3 rows)

so this time the amounts went to the proper places...
        regards, tom lane

Tom Lane wrote:

> Patrick JACQUOT <patrick.jacquot@anpe.fr> writes:
> > I found a simpler way to do the job I wanted done
>
> > UPDATE t1 SET  balance = balance+t2.amount WHERE EXISTS (SELECT *
> > FROM t2 WHERE t2.id = t1.id)
>
> > DOES WORK PROPERLY.
>
> Does it?  I don't think that this will do what you are expecting,

<snip>
I thank you very much for your advice. I didn't try indeed to
put more than one line in T2.
I tried before to use your approach bu i used the wrong syntax :
UPDATE t1 SET Balance=Balance+(SELECT Amount FROM t2 WHERE t2.id=t1.id);
which gave me 'parser : parser error near select'.
I am really sorry havig done such a mistake, and apologize
Next time i'll turn my tongue seven times in my mouth before
going to my keyboard.
Best regards
P. Jacquot