Re: request for sql3 compliance for the update command

Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
> Agreed folks are going to have bigger problems from Informix than just
> this, and in fact I used Informix for years and didn't know they allowed
> this.
>
> However, what solution do we have for UPDATE (coll...) = (select val...)
> for folks? It is awkward to repeat a query multiple times in an UPDATE.

hannu=# create table target (id serial, a int, b int, c int);
NOTICE: CREATE TABLE will create implicit sequence 'target_id_seq' for
SERIAL column 'target.id'
CREATE TABLE
hannu=# insert into target(a,b,c) values (0,0,0);
INSERT 16983 1
hannu=# insert into target(a,b,c) values (1,1,1);
INSERT 16984 1
hannu=# update target set
hannu-# a = source.a1, b=source.a2, c=source.a3
hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source
hannu-# where id = 1
hannu-# ;
UPDATE 1
hannu=# select * from target;
id | a | b | c
----+---+---+---
2 | 1 | 1 | 1
1 | 1 | 2 | 3
(2 rows)

hannu=#

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