Postgres Pro
Downloads
Home   >   mailing lists

Thread: update and select

update and select

From
Michael Meskes
Date:
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!

Re: [HACKERS] update and select

From
"Thomas G. Lockhart"
Date:
> 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

Re: [HACKERS] update and select

From
Michael Meskes
Date:
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!