Re: Help on update. - Mailing list pgsql-general

Hello.

First,we can not execute the SQL which Paulo indicated in PostgreSQL.

See this manual.
======================================================================

http://www.postgresql.org/docs/8.4/interactive/sql-update.html


Compatibility

This command conforms to the SQL standard, except that the FROM
and RETURNING clauses are PostgreSQL extensions.

According to the standard, the column-list syntax should allow a list
  of columns to be assigned from a single row-valued expression, such
as a sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =
     (SELECT last_name, first_name FROM salesmen
      WHERE salesmen.id = accounts.sales_id);

This is not currently implemented — the source must be a list of
independent expressions.

Some other database systems offer a FROM option in which the target
table is supposed to be listed again within FROM. That is not how
PostgreSQL interprets FROM. Be careful when porting applications that
  use this extension.
=========================================================================


So, I tried to following SQL, but I got error.

update test t1 set t1.j= (COALESCE(Lag(t2.j) over(order by
t2.j),null,0)  ) + t2.j   from test t2;
ERROR:  cannot use window function in UPDATE at character 36


If I use temporary table ,I can.
But I feel this way is not simple.

=========================================================================
ex) PostgreSQL is 8.4.4

--drop table test;
create table test(i int , j int);
insert into test values(1,2);
insert into test values(1,3);
insert into test values(1,4);

begin;
create temporary table test_temp (i int , j int);
insert into test_temp
SELECT i,COALESCE(Lag(j) over(order by j),null,0)  + j   from test;
truncate table test;
insert into test select * from test_temp;
drop table test_temp;
commit;
=========================================================================


Anyone have a good idea?


(2010/05/26 22:46), paulo matadr wrote:
>
> |create table test(i number , j number);|
> |insert into test values(1,2)
> ||insert into test values(1,3)
> ||insert into test values(1,4)
> |
> select * from test;
>           I          J
> ---------- ----------
>           1          2
>           1          3
>           1          4
>
> Myintentions:
> after update
> select * from test;
>           I          J
> ---------- ----------
>           1          2
>           1          3+2
>
>          1          4+3
> after
> select * from test;
>           I          J
> ---------- ----------
>           1          2
>           1          5+2
>           1          7+5
>
> In  oracle,I use this:|
> update test x
> set x.j = x.j + (select lag_j
> from (select i, j, nvl(lag(j) over (order by i,j) ,0) as lag_j
> from test) y
> where x.i = y.i and x.j = y.j)
>
> how can translate this for work in postgres?
>
> Thanks's
>
> Paul
> |
>
>
>
>
>
>
>
>
>
> Paulo
>
>


--
================================================
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
================================================