Let's say I have a table test like this:

i|c
-+-
2|A
0|
1|T
(3 rows)

and I want to use an update to do the following: set the attr c of the
record with number 0 to the value of attr c in the record with number 1.

In Oracle I'd be able to write:

update test set c = (select c from test where i = 1) where i = 0;

Is it correct that we do not allow this? Pardon me if this is already on
todo, but I just stumbled across this problem and I'm not sure I can work
around this by using the from clause (which btw Oracle doesn't even have).
Since we do have subselects this shouldn't be too difficult.

Michael
-- 
Dr. Michael Meskes      | Th.-Heuss-Str. 61, D-41812 Erkelenz | Go SF49ers!
Senior-Consultant       | business: Michael.Meskes@mummert.de | Go Rhein Fire!
Mummert+Partner         |  private: Michael.Meskes@usa.net    | Use Debian
Unternehmensberatung AG |           Michael.Meskes@gmx.net    | GNU/Linux!

Tue, 3 Nov 1998 10:27:19 +0100
Michael Meskes <meskes@usa.net>
update and select
> In Oracle I'd be able to write:
> update test set c = (select c from test where i = 1) where i = 0;
> Is it correct that we do not allow this?

That is correct, and it is on the ToDo list as something like "allow
subselects in target expressions" (though I'm not finding it when I
look).

However, as you suspect you can rephrase it:

tgl=> update x set c = j.c from x as j where j.i = 1 and x.i = 0;
UPDATE 1
tgl=> select * from x;
i|c
-+-
1|T
2|A
0|T
(3 rows)

                    - Tom

Wed, 04 Nov 1998 07:25:24 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] update and select
On Wed, Nov 04, 1998 at 07:25:24AM +0000, Thomas G. Lockhart wrote:
> That is correct, and it is on the ToDo list as something like "allow
> subselects in target expressions" (though I'm not finding it when I
> look).

Would be nice to have, yes.

> However, as you suspect you can rephrase it:
> 
> tgl=> update x set c = j.c from x as j where j.i = 1 and x.i = 0;

True. But unfortunately that won't help me as I was writing an SQL script to
be run on Oracle later on. :-)

Michael
-- 
Dr. Michael Meskes      | Th.-Heuss-Str. 61, D-41812 Erkelenz | Go SF49ers!
Senior-Consultant       | business: Michael.Meskes@mummert.de | Go Rhein Fire!
Mummert+Partner         |  private: Michael.Meskes@usa.net    | Use Debian
Unternehmensberatung AG |           Michael.Meskes@gmx.net    | GNU/Linux!

Wed, 4 Nov 1998 12:27:34 +0100
Michael Meskes <meskes@usa.net>
Re: [HACKERS] update and select