pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate help
12+ messages / 10 participants
[nested] [flat]
* update help
@ 2001-01-18 02:54 Carolyn Wong <carolyn@greatpacific.com.au>
2001-01-18 03:17 ` Re: update help Tubagus Nizomi <nizomi@dnet.net.id>
2001-01-18 03:18 ` Re: update help Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: Carolyn Wong @ 2001-01-18 02:54 UTC (permalink / raw)
To: pgsql-sql
I'd like to know what's the correct SQL statement to do the following:
update t1 a
set a.amount = sum(b.amount)
from t2 b
where a.id = b.id
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update help
2001-01-18 02:54 update help Carolyn Wong <carolyn@greatpacific.com.au>
@ 2001-01-18 03:17 ` Tubagus Nizomi <nizomi@dnet.net.id>
2001-01-18 03:58 ` Re: update help Carolyn Lu Wong <carolyn@greatpacific.com.au>
1 sibling, 1 reply; 12+ messages in thread
From: Tubagus Nizomi @ 2001-01-18 03:17 UTC (permalink / raw)
To: pgsql-sql
update t1
set amount = sum(b.amount)
from ts b
where a.id=b.id
On Thursday 18 January 2001 09:54, Carolyn Wong wrote:
> I'd like to know what's the correct SQL statement to do the following:
>
> update t1 a
> set a.amount = sum(b.amount)
> from t2 b
> where a.id = b.id
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update help
2001-01-18 02:54 update help Carolyn Wong <carolyn@greatpacific.com.au>
2001-01-18 03:17 ` Re: update help Tubagus Nizomi <nizomi@dnet.net.id>
@ 2001-01-18 03:58 ` Carolyn Lu Wong <carolyn@greatpacific.com.au>
0 siblings, 0 replies; 12+ messages in thread
From: Carolyn Lu Wong @ 2001-01-18 03:58 UTC (permalink / raw)
To: Tubagus Nizomi <nizomi@dnet.net.id>; +Cc: pgsql-sql
This update field with the sum of all amounts in t2. I want to update
sum of each individual IDs.
Tubagus Nizomi wrote:
>
> update t1
> set amount = sum(b.amount)
> from ts b
> where a.id=b.id
>
> On Thursday 18 January 2001 09:54, Carolyn Wong wrote:
> > I'd like to know what's the correct SQL statement to do the following:
> >
> > update t1 a
> > set a.amount = sum(b.amount)
> > from t2 b
> > where a.id = b.id
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update help
2001-01-18 02:54 update help Carolyn Wong <carolyn@greatpacific.com.au>
@ 2001-01-18 03:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-18 04:01 ` Re: update help Carolyn Lu Wong <carolyn@greatpacific.com.au>
2001-01-18 04:04 ` Re: update help Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 12+ messages in thread
From: Tom Lane @ 2001-01-18 03:18 UTC (permalink / raw)
To: Carolyn Wong <carolyn@greatpacific.com.au>; +Cc: pgsql-sql
Carolyn Wong <carolyn@greatpacific.com.au> writes:
> I'd like to know what's the correct SQL statement to do the following:
> update t1 a
> set a.amount = sum(b.amount)
> from t2 b
> where a.id = b.id
Try
UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id);
Or possibly you want
UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id)
WHERE EXISTS (select * FROM t2 b WHERE t1.id = b.id);
depending on whether you mean to zero out any t1 rows that have no
matching rows in t2. Note that you cannot attach an alias name to the
target table, you have to use its real name in the subselects.
There was a thread about this very issue a few months ago, and IIRC
we decided that an aggregate in an UPDATE doesn't have well-defined
semantics. The SQL92 spec explicitly disallows it. Right now PG will
take it, but we probably do something pretty surprising :-(
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update help
2001-01-18 02:54 update help Carolyn Wong <carolyn@greatpacific.com.au>
2001-01-18 03:18 ` Re: update help Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-01-18 04:01 ` Carolyn Lu Wong <carolyn@greatpacific.com.au>
2001-01-18 04:18 ` Re: update help Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Carolyn Lu Wong @ 2001-01-18 04:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Forgot to mention that I'm using V6.5. It doesn't seem to like
subqueries, got the following error:
ERROR: parser: parse error at or near "select"
What I really want to do is follows
t2: ID Amount ........
-----------------------
1 1 ......
1 2 ......
2 3 ......
2 2 ......
and want the following result in t1
t1: ID Amount .......
-----------------------
1 3 ......
2 5 ......
Tom Lane wrote:
>
> Carolyn Wong <carolyn@greatpacific.com.au> writes:
> > I'd like to know what's the correct SQL statement to do the following:
> > update t1 a
> > set a.amount = sum(b.amount)
> > from t2 b
> > where a.id = b.id
>
> Try
>
> UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id);
>
> Or possibly you want
>
> UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id)
> WHERE EXISTS (select * FROM t2 b WHERE t1.id = b.id);
>
> depending on whether you mean to zero out any t1 rows that have no
> matching rows in t2. Note that you cannot attach an alias name to the
> target table, you have to use its real name in the subselects.
>
> There was a thread about this very issue a few months ago, and IIRC
> we decided that an aggregate in an UPDATE doesn't have well-defined
> semantics. The SQL92 spec explicitly disallows it. Right now PG will
> take it, but we probably do something pretty surprising :-(
>
> regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update help
2001-01-18 02:54 update help Carolyn Wong <carolyn@greatpacific.com.au>
2001-01-18 03:18 ` Re: update help Tom Lane <tgl@sss.pgh.pa.us>
2001-01-18 04:01 ` Re: update help Carolyn Lu Wong <carolyn@greatpacific.com.au>
@ 2001-01-18 04:18 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2001-01-18 04:18 UTC (permalink / raw)
To: Carolyn Lu Wong <carolyn@greatpacific.com.au>; +Cc: pgsql-sql
Carolyn Lu Wong <carolyn@greatpacific.com.au> writes:
> Forgot to mention that I'm using V6.5.
Oh. 6.5's support for sub-selects is pretty limited :-(.
I think the only way to do it in 6.5 is with a temp table,
eg
SELECT id, sum(amount) as sum into temp table tt from t2 group by id;
update t1 set amount = tt.sum where id = tt.id;
drop table tt;
You might wanna think about updating sometime soon...
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update help
2001-01-18 02:54 update help Carolyn Wong <carolyn@greatpacific.com.au>
2001-01-18 03:18 ` Re: update help Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-01-18 04:04 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 12+ messages in thread
From: Josh Berkus @ 2001-01-18 04:04 UTC (permalink / raw)
To: pgsql-sql
Tom,
> UPDATE t1 SET amount = (select sum(b.amount) from t2 b
> WHERE t1.id = b.id);
Interesting. I'm used to (not necessarily in PGSQL):
UPDATE t1 SET amount = t2ttl.totalamount
FROM (SELECT sum(amount) as totalamount,
id FROM t2 GROUP BY id) t2ttl
WHERE t1.id = t2.id
Although this is a subselect in the FROM clause, something
we've had trouble with as I recall.
-Josh Berkus
^ permalink raw reply [nested|flat] 12+ messages in thread
* Update help
@ 2002-09-03 16:19 Yudieg@hotmail.com <yudieg@hotmail.com>
2002-09-05 17:29 ` Re: Update help Ligia Pimentel <pimentel_ligia@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Yudieg@hotmail.com @ 2002-09-03 16:19 UTC (permalink / raw)
To: pgsql-sql
Hi,
Let say I have 2 Product table, both of them has columns ProductID and Price
What is the update command if I want to update all Prices of first table to be equal with Price in second table?
Please Help.
Yudie
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update help
2002-09-03 16:19 Update help Yudieg@hotmail.com <yudieg@hotmail.com>
@ 2002-09-05 17:29 ` Ligia Pimentel <pimentel_ligia@hotmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Ligia Pimentel @ 2002-09-05 17:29 UTC (permalink / raw)
To: pgsql-sql
update table1 set price = table2.price
where table1.productId = table2.productId;
(Of course, both tables must have a different name).
:)
Ligia
""Yudieg@hotmail.com"" <yudieg@hotmail.com> wrote in message
news:OE50r7MoNYXRRS6HtHO00000811@hotmail.com...
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_00AA_01C2533B.BE837FA0
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
> Let say I have 2 Product table, both of them has columns ProductID and
Price
> What is the update command if I want to update all Prices of first table
to=
> be equal with Price in second table?
>
> Please Help.
>
> Yudie
> ------=_NextPart_000_00AA_01C2533B.BE837FA0
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html;
charset=3Diso-8859-1">
> <META content=3D"MSHTML 5.50.4916.2300" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>Let say I have 2 Product table, both of
th=
> em has=20
> columns ProductID and Price</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>What is the update command if I want to
up=
> date=20
> all Prices of first table to be equal with Price in second=20
> table?</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT face=3DArial size=3D2>Please Help.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Yudie</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_00AA_01C2533B.BE837FA0--
^ permalink raw reply [nested|flat] 12+ messages in thread
* Update Help
@ 2002-09-03 16:38 Yudie@axiontech.com <yudie@axiontech.com>
2002-09-03 18:01 ` Re: Update Help Oliver Elphick <olly@lfix.co.uk>
2002-09-03 18:55 ` Re: Update Help Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 12+ messages in thread
From: Yudie@axiontech.com @ 2002-09-03 16:38 UTC (permalink / raw)
To: pgsql-sql
Hi,
Let say I have 2 Product table, both of them has columns ProductID and Price
What is the update command if I want to update all Prices of first table to be equal with Price in second table?
Please Help.
Yudie
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update Help
2002-09-03 16:38 Update Help Yudie@axiontech.com <yudie@axiontech.com>
@ 2002-09-03 18:01 ` Oliver Elphick <olly@lfix.co.uk>
1 sibling, 0 replies; 12+ messages in thread
From: Oliver Elphick @ 2002-09-03 18:01 UTC (permalink / raw)
To: Yudie@axiontech.com <yudie@axiontech.com>; +Cc: pgsql-sql
On Tue, 2002-09-03 at 17:38, Yudie@axiontech.com wrote:
> Let say I have 2 Product table, both of them has columns ProductID and
Price
> What is the update command if I want to update all Prices of first
table to be equal with Price in second table?
UPDATE table1
SET price =
(SELECT price
FROM table2
WHERE table1.product = table2.product);
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And he said unto his disciples, Therefore I say unto
you, Take no thought for your life, what ye shall eat;
neither for the body, what ye shall put on. For life
is more than meat, and the body is more than clothing.
Consider the ravens, for they neither sow nor reap;
they have neither storehouse nor barn; and yet God
feeds them; how much better you are than the birds!
Consider the lilies, how they grow; they toil
not, they spin not; and yet I say unto you, that
Solomon in all his glory was not arrayed like one of
these. If then God so clothe the grass, which is to
day in the field, and tomorrow is cast into the oven;
how much more will he clothe you, O ye of little
faith? And seek not what ye shall eat, or what ye
shall drink, neither be ye of doubtful mind.
But rather seek ye the kingdom of God; and all these
things shall be added unto you."
Luke 12:22-24; 27-29; 31.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update Help
2002-09-03 16:38 Update Help Yudie@axiontech.com <yudie@axiontech.com>
@ 2002-09-03 18:55 ` Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 12+ messages in thread
From: Peter Eisentraut @ 2002-09-03 18:55 UTC (permalink / raw)
To: Yudie@axiontech.com <yudie@axiontech.com>; +Cc: pgsql-sql
Yudie@axiontech.com writes:
> Let say I have 2 Product table, both of them has columns ProductID and
> Price What is the update command if I want to update all Prices of first
> table to be equal with Price in second table?
Possibly you mean something like this:
UPDATE first_table SET price = (SELECT price FROM second_table WHERE
second_table.productid = first_table.productid);
Possibly the answer is also to redesign your schema to avoid redundant
data.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2002-09-05 17:29 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-18 02:54 update help Carolyn Wong <carolyn@greatpacific.com.au>
2001-01-18 03:17 ` Tubagus Nizomi <nizomi@dnet.net.id>
2001-01-18 03:58 ` Carolyn Lu Wong <carolyn@greatpacific.com.au>
2001-01-18 03:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-18 04:01 ` Carolyn Lu Wong <carolyn@greatpacific.com.au>
2001-01-18 04:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-18 04:04 ` Josh Berkus <josh@agliodbs.com>
2002-09-03 16:19 Update help Yudieg@hotmail.com <yudieg@hotmail.com>
2002-09-05 17:29 ` Re: Update help Ligia Pimentel <pimentel_ligia@hotmail.com>
2002-09-03 16:38 Update Help Yudie@axiontech.com <yudie@axiontech.com>
2002-09-03 18:01 ` Re: Update Help Oliver Elphick <olly@lfix.co.uk>
2002-09-03 18:55 ` Re: Update Help Peter Eisentraut <peter_e@gmx.net>
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