如何根据B行的值，更新A行的值
各位大大，现有个问题，处理不了了，求帮忙
tab1表如下(a,d为主键)：
a b c d
000000 2020 a
010000 1515 a
010400 5 5 a
000000 2525 b
010000 1515 b
010400 5 5 b
000000 2525 c
010000 1515 c
010400 5 0 c
如果a字段值为010400时，b=c，则a字段010400的b和c的值设为0；
a字段010000 的b字段减去010400的b的值,c字段减去010400的c字段的值；
a字段000000 的b字段减去010400的b的值，c字段减去010400的c字段的值
;
如不等则不作修改
如何通过多个update语句实现？
结果为
a b c d
000000 1515 a
010000 1010 a
010400 00 a
000000 2020 b
010000 1010 b
010400 00 b
000000 2525 c
010000 1515 c
010400 5 0 c
update tab1 B set b=b-(select b from tab1 A where b=c and A.a=010400 and A.d=B.d ) where B.a=000000;
update tab1 B set c=c-(select c from tab1 A where b=c and A.a=010400 and A.d=B.d ) where B.a=000000;
上面的语句可以么？
[解决办法]
先更新a字段010000 的
a字段000000的,
在更新a字段010400的
- SQL code
update tab1 B set b=b-(select max(b) from tab1 A where A.b=A.c and A.a=010400) ,c=c-(select max(c) from tab1 A where A.b=A.c and A.a=010400) where a=010000;update tab1 B set b=b-(select max(b) from tab1 A where A.b=A.c and A.a=010400) ,c=c-(select max(c) from tab1 A where A.b=A.c and A.a=010400) where a=000000;update tab1 B set b=0 ,c=0where a=000000 and b=c;
[解决办法]
实测数据
- SQL code
CREATE TABLE T134( a VARCHAR2(20), b NUMBER(4), c NUMBER(4), d VARCHAR2(20));INSERT INTO T134 VALUES('000000', 20, 20, 'a');INSERT INTO T134 VALUES('010000', 15, 15, 'a');INSERT INTO T134 VALUES('010400', 5, 5, 'a');INSERT INTO T134 VALUES('000000', 25, 25, 'b');INSERT INTO T134 VALUES('010000', 15, 15, 'b');INSERT INTO T134 VALUES('010400', 5, 5, 'b');INSERT INTO T134 VALUES('000000', 20, 20, 'c');INSERT INTO T134 VALUES('010000', 15, 15, 'c');INSERT INTO T134 VALUES('010400', 5, 0, 'c');-- 更新a = '0000000' 和a='010000'的行UPDATE T134 T1 SET b = b - (SELECT b FROM T134 T2 WHERE b = c AND a = '010400' AND T2.d = T1.d), c = c - (SELECT c FROM T134 T3 WHERE b = c AND a = '010400' AND T3.d = T1.d)WHERE (a = '000000' OR a = '010000') AND EXISTS(SELECT 1 FROM T134 T4 WHERE b = c AND a = '010400' AND T4.d = T1.d)-- 更新 a = '010400'的行UPDATE T134 SET b = 0, c = 0 WHERE b = c AND a = '010400';