假设 A(ID,NAME,score) B(ID,SCORE) ,具体不知道你要什么。
不过也许你想要的是这个:
update a set (score)=(select score from b where b.id=a.id);
-----
SQL> CREATE TABLE TEST_A(ID INTEGER,NAME VARCHAR2(10),SCORE INTEGER);Table createdSQL> Create table test_b(id integer,score integer);Table createdSQL> insert into test_a values(1,'How',null);1 row insertedSQL> insert into test_a values(2,'do you du?',null);1 row insertedSQL> insert into test_a values(3,'GoodName',null);1 row insertedSQL> commit;Commit completeSQL> insert into test_b values(1,100);1 row insertedSQL> insert into test_b values(2,99);1 row insertedSQL> insert into test_b values(3,58);1 row insertedSQL> commit;Commit completeSQL> update test_a set (score)=(select score from test_b where test_b.id=test_a.id);3 rows updatedSQL> commit;Commit completeSQL> select * from test_a; ID NAME SCORE
--------------------------------------- ---------- ---------------------------------------
1 How 100
2 do you du? 99
3 GoodName 58
不过也许你想要的是这个:
update a set (score)=(select score from b where b.id=a.id);
-----
SQL> CREATE TABLE TEST_A(ID INTEGER,NAME VARCHAR2(10),SCORE INTEGER);Table createdSQL> Create table test_b(id integer,score integer);Table createdSQL> insert into test_a values(1,'How',null);1 row insertedSQL> insert into test_a values(2,'do you du?',null);1 row insertedSQL> insert into test_a values(3,'GoodName',null);1 row insertedSQL> commit;Commit completeSQL> insert into test_b values(1,100);1 row insertedSQL> insert into test_b values(2,99);1 row insertedSQL> insert into test_b values(3,58);1 row insertedSQL> commit;Commit completeSQL> update test_a set (score)=(select score from test_b where test_b.id=test_a.id);3 rows updatedSQL> commit;Commit completeSQL> select * from test_a; ID NAME SCORE
--------------------------------------- ---------- ---------------------------------------
1 How 100
2 do you du? 99
3 GoodName 58
set (col1, col2, col3, ...) =
(select col1, col2, col3,...
from test_b where test_b.primary_key = test_a.primary_key )
where exists (select 1 from test_b where test_b.primary_key = test_a.primary_key );
set (col1, col2, col3, ...) =
(select col1, col2, col3,...
from test_b where test_b.primary_key = test_a.primary_key )这样不行吗?
set (col1, col2, col3, ...) =
(select col1, col2, col3,...
from test_b where test_b.primary_key = test_a.primary_key )这样也行??
update a set (c1,c2,c3..Cn)=(select c1,c2,c3..cn from b where b.key=a.key)