求教sql语句 更新update set =select？

[「已注销」]

sql需求。。更新A表中字段money信息，value值为A表中money的值 + 从B表中select获取的money，
两表通过userid关联，两个字段都是int类型。。

UPDATE A set money= (22+22) WHERE A.userid=B.userid;
诸如这种，我将（）中+两边换成select语句之后怎么都不行，求解？

[卖水果的net]

-- 大概如下，你试一下
update a
set a.money = a.money + b.money
from a,b 
where a.id = b.id

[ORAClE SE]

--楼上错了，不好意思。
DECLARE
  CURSOR a IS
    SELECT userid,money FROM b;
  val a%ROWTYPE;
BEGIN
  OPEN a;
  LOOP
    FETCH a
      INTO val;
    EXIT WHEN a%NOTFOUND;
    UPDATE a SET money = val.money WHERE userid = val.userid;
    COMMIT;
  END LOOP;
  CLOSE a;
END;
/ 
COMMIT;
EXIT

[sinat_18597213]

UPDATE a SET a.money=a.money+(SELECT money FROM b WHERE a.id1=b.id1)

[ls_rose]

楼上正解...

[hoyuleds]

DECLARE
CURSOR a IS
SELECT userid,money FROM b;
val a%ROWTYPE;
BEGIN
OPEN a;
LOOP
FETCH a
INTO val;
EXIT WHEN a%NOTFOUND;
UPDATE a SET money = val.money WHERE userid = val.money;
COMMIT;
END LOOP;
CLOSE a;
END;
/
COMMIT;
EXIT

[hoyuleds]

--楼上错了，不好意思。
DECLARE
CURSOR a IS
SELECT userid,money FROM b;
val a%ROWTYPE;
BEGIN
OPEN a;
LOOP
FETCH a
INTO val;
EXIT WHEN a%NOTFOUND;
UPDATE a SET money = val.money WHERE userid = val.userid;
COMMIT;
END LOOP;
CLOSE a;
END;
/
COMMIT;
EXIT

[东方淄墨]

UPDATE a SET a.money=a.money+(SELECT money FROM b WHERE a.userid=b.userid)

[bo871747022]

merge into a using b on (a.id = b.id) when matched then update set a.money= a.money+ b.money

[zy18755122285]

[zkzhong]

试试看： UPDATE A SET A.money= A.money+( SELECT money FROM B WHERE A.userid=B.userid) ;

[布莱恩凯]

update a set a.money=a.money+(select money from b where b.userid=a.userid) where exists(select 1 from b c where c.userid=a.userid)

[ssqtjffcu]

引用 10 楼 zzklovehrr 的回复:

试试看： UPDATE A SET A.money= A.money+( SELECT money FROM B WHERE A.userid=B.userid) ;

你这语句很危险啊，要是b表没有找到对应记录，岂不是把a表的money字段更新为null了

[luofenghen]

update A SET A.money=(SELECT B.money FROM B WHERE A.userid=B.userid)

[ZhanYunQI]

update A SET A.money=NVL((SELECT B.money FROM B WHERE A.userid=B.userid),0) WHERE A表CONDITION

[关山万里]

楼上正解..