pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect in update
4+ messages / 2 participants
[nested] [flat]
* select in update
@ 1998-11-23 23:41 Engard Ferenc <fery@pons.sote.hu>
0 siblings, 1 reply; 4+ messages in thread
From: Engard Ferenc @ 1998-11-23 23:41 UTC (permalink / raw)
To: pgsql-sql@postgreSQL.org, Pgsql Novice <pgsql-novice@postgreSQL.org>
Hi all,
Yet another question: (quite busy today... :)
oeptest=> update shorttest2 set b=(select b from shorttest t1 where t1.a=1) where a=2;
ERROR: parser: parse error at or near "select"
It means that the subquery in update is not implemented? And if it isn't,
how can I evade it? Maybe select ... into .., but it's not a good idea in a
big table... :(((
Any help?
Thx a lot:
Circum
__ @
/ \ _ _ Engard Ferenc
l | ( \ / | | (\/) mailto:s-fery@kkt.sote.hu
\__/ | | \_ \_/ I I http://pons.sote.hu/~s-fery
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] select in update
@ 1998-11-24 08:47 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Engard Ferenc <fery@pons.sote.hu>
0 siblings, 2 replies; 4+ messages in thread
From: Herouth Maoz @ 1998-11-24 08:47 UTC (permalink / raw)
To: s-fery@kkt.sote.hu; +Cc: pgsql-sql
At 1:41 +0200 on 24/11/98, Engard Ferenc wrote:
> Yet another question: (quite busy today... :)
>
> oeptest=> update shorttest2 set b=(select b from shorttest t1 where
>t1.a=1) where a=2;
> ERROR: parser: parse error at or near "select"
>
> It means that the subquery in update is not implemented? And if it isn't,
> how can I evade it? Maybe select ... into .., but it's not a good idea in a
> big table... :(((
>
> Any help?
The long-standing syntax for this in PostgreSQL is:
UPDATE shorttest2
SET b=t1.b
FROM shorttest t1
WHERE t1.a=1;
Please, look up the manpages for the command you want to invoke, or \h it.
You may discover syntax options that cover what you need. I have PostgreSQL
6.2.1, and running "\h update" in psql yields:
testing=> \h update
Command: update
Description: update tuples
Syntax:
update <class_name> set <attr1>=<expr1>,...<attrN>=<exprN> [from <from_clause>]
[where <qual>];
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] select in update
@ 1998-11-24 10:05 Engard Ferenc <fery@pons.sote.hu>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 4+ messages in thread
From: Engard Ferenc @ 1998-11-24 10:05 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; pgsql-sql
On Tue, 24 Nov 1998, Herouth Maoz wrote:
>The long-standing syntax for this in PostgreSQL is:
>
>UPDATE shorttest2
>SET b=t1.b
>FROM shorttest t1
>WHERE t1.a=1;
>
>Please, look up the manpages for the command you want to invoke, or \h it.
>You may discover syntax options that cover what you need. I have PostgreSQL
Thx for the help! Anyway, I have read the docs, but since there is no
example there, and I didn't know this syntax (and maybe my english is not
so good), I couldn't figure out what is this strange FROM clausule... :))
Well, then a little bit complex problem:
two tables: A (var), B (var1,var2). I would like to replace the A.var
if it exists in B.var2 with B.var1. Is it good?
UPDATE a
SET a.var=b.var1
FROM b <-- or maybe I need to put 'a' here too?
WHERE a.var=b.var2;
I don't need to tell that there are rows which cannot find in B? (In
this case it shouldn't change.)
I feel that I need to write two different WHERE-s: one for the subquery
(which then results 1 row), and one for the UPDATE, to tell where to
put that value. I solved this until now with a 'c' prg.
Thanks again and bye:
Circum
__ @
/ \ _ _ Engard Ferenc
l | ( \ / | | (\/) mailto:s-fery@kkt.sote.hu
\__/ | | \_ \_/ I I http://pons.sote.hu/~s-fery
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] select in update
@ 1998-11-24 11:27 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 4+ messages in thread
From: Herouth Maoz @ 1998-11-24 11:27 UTC (permalink / raw)
To: pgsql-sql
At 12:05 +0200 on 24/11/98, Engard Ferenc wrote:
> Well, then a little bit complex problem:
> two tables: A (var), B (var1,var2). I would like to replace the A.var
> if it exists in B.var2 with B.var1. Is it good?
>
> UPDATE a
> SET a.var=b.var1
> FROM b <-- or maybe I need to put 'a' here too?
> WHERE a.var=b.var2;
>
> I don't need to tell that there are rows which cannot find in B? (In
> this case it shouldn't change.)
It's a bit hard to understand what you want to be done, and what you
*don't* want to be done.
The above seems to be the right update. But you better give a short example
of what you want. In the example you gave, if the data is:
A
==
1
2
3
4
B
==
var1 var2
==== ====
10 1
30 3
50 5
Then after the update, A will be:
10
2
30
4
This is because 1 and 3 were matched, and 2 and 4 weren't matched. If that
is what you wanted, then your update statement is correct.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1998-11-24 11:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-23 23:41 select in update Engard Ferenc <fery@pons.sote.hu>
1998-11-24 08:47 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-11-24 10:05 ` Engard Ferenc <fery@pons.sote.hu>
1998-11-24 11:27 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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