Re: request for sql3 compliance for the update command

Bruce Momjian kirjutas E, 17.03.2003 kell 20:49:
> With no one replying on how to do correlated subqueries in FROM for
> UPDATE,

Correlated subqueries not working in FROM cluse of UPDATE is IMHO a bug,
so the way to do correlated subqueries in FROM for UPDATE would be to
fix this bug ;)

All common sense tells me that if I can update set col1=col2 and *not*
get the value from the first col2 to all col1's then the same should be
true for this

hannu=# creatre table updtarget(
hannu(# id int, val text);
ERROR:  parser: parse error at or near "creatre" at character 1
hannu=# create table updtarget(id int, val text);
CREATE TABLE
hannu=# create table updsource(id int, val text);
CREATE TABLE
hannu=# insert into updtarget(id) values (1);
INSERT 16995 1
hannu=# insert into updtarget(id) values (2);
INSERT 16996 1
hannu=# insert into updsource(id,val) values (1,'one');
INSERT 16997 1
hannu=# insert into updsource(id,val) values (2,'two');
INSERT 16998 1
hannu=# update updtarget set val = src.val
hannu-# from (select s.val from updsource s
hannu-#        where s.id=updtarget.id) as src
hannu-# ;
NOTICE:  Adding missing FROM-clause entry in subquery for table
"updtarget"
UPDATE 2
hannu=# select * from updtarget;id | val
----+----- 1 | one 2 | one
(2 rows)

there should be no need to add "missing FROM-clause entry"  and the
result *should* be:

hannu=# select * from updtarget;id | val
----+----- 1 | one 2 | two
(2 rows)

--------------------
Hannu