Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE MEMBER (
member_no   NUMBER  PRIMARY KEY ,
member_money NUMBER NOT NULL
);
CREATE TABLE MY_PAY(
pay_no NUMBER PRIMARY KEY,
member_no NUMBER,
pay_price number
);
INSERT INTO member VALUES (2, 2000);
1 rows affected

INSERT INTO my_pay
SELECT 1, 2, 100 FROM DUAL UNION ALL
SELECT 2, 2, 200 FROM DUAL;
2 rows affected

UPDATE MEMBER m
set member_money = member_money - (select sum(pay_price)
                                   from   MY_PAY p
                                   where  p.member_no = m.member_no)
where  member_no =2 ;
1 rows affected

SELECT * FROM member;


MEMBER_NO
MEMBER_MONEY


2
1700