Help on update.

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
================================================

Hello,
While I can't answer my question, I am afraid I did not understand your
intentions either. The update you mentioned may not return consistent
results, isn't it? You are not ordering the data in any particular manner
and there is no unique column either. So the result of the update will
depend on the order in which the data is fetched - which need not be
consistent?
If data is fetched as
I J
---------- ----------
1 2
1 3
1 4
update will result in one set of data.
1 2
1 5
1 7
If the query returns
I J
---------- ----------
1 4
1 2
1 3

the result of the update will be different?
1 4
1 6
1 5
Regards,
Jayadevan

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."