UPDATE test a
SET (a.v1,a.v2) =
(SELECT SUM(DECODE(id,2,-v1,v1)),
SUM(DECODE(id,2,-v2,v2))
FROM test b
WHERE b.name = a.name)
WHERE a.id = 4
SET (a.v1,a.v2) =
(SELECT SUM(DECODE(id,2,-v1,v1)),
SUM(DECODE(id,2,-v2,v2))
FROM test b
WHERE b.name = a.name)
WHERE a.id = 4
可不可以采用这样的表结构.
create table test(
nam varchar2(3),
v1_1 integer,
v1_2 integer,
v1_3 integer,
v1_4 integer,
v2_1 integer,
v2_2 integer,
v2_3 integer,
v2_4 integer)
然后:
insert into test values('aaa',10,5,2,0,8,2,3,0);
SET (a.v1,a.v2) =
(SELECT SUM(DECODE(id,2,-v1,v1)),
SUM(DECODE(id,2,-v2,v2))
FROM test b
WHERE b.id < 4
and b.name = a.name)
WHERE a.id = 4