pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfound a way to update a table with data from another one
3+ messages / 2 participants
[nested] [flat]
* found a way to update a table with data from another one
@ 1999-11-30 12:07 Patrick JACQUOT <patrick.jacquot@anpe.fr>
1999-11-30 15:22 ` Re: [SQL] found a way to update a table with data from another one Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Patrick JACQUOT @ 1999-11-30 12:07 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] found a way to update a table with data from another one
1999-11-30 12:07 found a way to update a table with data from another one Patrick JACQUOT <patrick.jacquot@anpe.fr>
@ 1999-11-30 15:22 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-01 09:32 ` Re: [SQL] found a way to update a table with data from another one Patrick JACQUOT <patrick.jacquot@anpe.fr>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 1999-11-30 15:22 UTC (permalink / raw)
To: Patrick JACQUOT <patrick.jacquot@anpe.fr>; +Cc: pgsql-sql
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| 100
2| 1000
3| 0
(3 rows)
regression=> select * from t2;
id|amount
--+------
1| 44
2| 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| 0
1| 144
2| 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| 0
1| 188
2| 1099
(3 rows)
so this time the amounts went to the proper places...
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] found a way to update a table with data from another one
1999-11-30 12:07 found a way to update a table with data from another one Patrick JACQUOT <patrick.jacquot@anpe.fr>
1999-11-30 15:22 ` Re: [SQL] found a way to update a table with data from another one Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-12-01 09:32 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
0 siblings, 0 replies; 3+ messages in thread
From: Patrick JACQUOT @ 1999-12-01 09:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-12-01 09:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-30 12:07 found a way to update a table with data from another one Patrick JACQUOT <patrick.jacquot@anpe.fr>
1999-11-30 15:22 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-01 09:32 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox