update Table2 t set t.AGE = (select t1.AGE from table1 t1 where t.NAME =t1.NAME) where t.NAME in (select t1.NAME from table1 t1)
ID NAME AGE 1 小明 18 2 小張 20 3 小王 22--Table2 ID NAME AGE 001 小明 002 小王 003 小張 18drop table t2; create table t1( id int, name varchar2(20), age int ); create table t2( id int, name varchar2(20), age int ); insert into t1 values (1,'小明',18); insert into t1 values (2,'小張',20); insert into t1 values (3,'小王',22); insert into t2 values (1,'小明',null); insert into t2 values (2,'小王',null); insert into t2 values (3,'小張',18); commit; update t2 set t2.age = (select t1.age from t1 where t1.name = t2.name and rownum = 1) where exists (select 1 from t1 where t1.name = t2.name); commit; select * from t2;
ID NAME AGE --------------------------------------- -------------------- --------------------------------------- 1 小明 18 2 小王 22 3 小張 20
update Table2 t set t.AGE = (select t1.AGE from table1 t1 where t.NAME =t1.NAME) where t.NAME in (select t1.NAME from table1 t1)
不好意思,我題目出的可能和我實際的問題有點出入,應該是這樣... TABLE1 ID NAME AGE 1 小張 18 2 小王 20 3 小明 22TABLE2 ID NAME AGE 001 小王 18 002 小明 20 003 小明 004 小張 22表結構應該是這樣的,TABLE1的NAME不會重複,而TABLE2的NAME會重複,要根據TABLE1和TABLE2的NAME把TABLE1的AGE賦到TABLE2對應的AGE上..
where t.NAME in (select t1.NAME from table1 t1)
ID NAME AGE
1 小明 18
2 小張 20
3 小王 22--Table2
ID NAME AGE
001 小明
002 小王
003 小張 18drop table t2;
create table t1(
id int,
name varchar2(20),
age int
);
create table t2(
id int,
name varchar2(20),
age int
);
insert into t1 values (1,'小明',18);
insert into t1 values (2,'小張',20);
insert into t1 values (3,'小王',22);
insert into t2 values (1,'小明',null);
insert into t2 values (2,'小王',null);
insert into t2 values (3,'小張',18);
commit;
update t2
set t2.age = (select t1.age
from t1
where t1.name = t2.name
and rownum = 1)
where exists (select 1 from t1 where t1.name = t2.name);
commit;
select * from t2;
ID NAME AGE
--------------------------------------- -------------------- ---------------------------------------
1 小明 18
2 小王 22
3 小張 20
where t.NAME in (select t1.NAME from table1 t1)
TABLE1
ID NAME AGE
1 小張 18
2 小王 20
3 小明 22TABLE2
ID NAME AGE
001 小王 18
002 小明 20
003 小明
004 小張 22表結構應該是這樣的,TABLE1的NAME不會重複,而TABLE2的NAME會重複,要根據TABLE1和TABLE2的NAME把TABLE1的AGE賦到TABLE2對應的AGE上..