如有表 A1(CODE,NAME)
数据
CODE Name
0001 A1
0002 A1
0003 A2我有另一个表B1(BCode,BNAME)
数据为:
BCODE BNAME
S1 A1
S2 A1我现在想将A1与B1关联,更新成
A1数据:
CODE NAME
0001 S1
0002 S2
0003 A2
这样形式的。我应该怎么写这个语句,请指教!!!,最好能写的具体点儿。
数据
CODE Name
0001 A1
0002 A1
0003 A2我有另一个表B1(BCode,BNAME)
数据为:
BCODE BNAME
S1 A1
S2 A1我现在想将A1与B1关联,更新成
A1数据:
CODE NAME
0001 S1
0002 S2
0003 A2
这样形式的。我应该怎么写这个语句,请指教!!!,最好能写的具体点儿。
insert into A1 values('0001','A1');
insert into A1 values('0002','A1');
insert into A1 values('0003','A2');
commit;
create table B1(BCode varchar2(10),BNAME varchar2(10));
insert into B1 values('S1','A1');
insert into B1 values('S2','A1');
commit;update a1 t1 set name=(
select t3.BCODE from
(select code,name,count(name)
over(partition by name order by code,name)
newName from a1) t2,
(select BCode,BNAME,count(Bname)
over(partition by BNAME order by BCode,Bname)
newBName from b1) t3
where t1.CODE=t2.code
and t2.name=t3.bname
and t2.newname=t3.newbname)
where exists(
select 1 from
(select code,name,count(name)
over(partition by name order by code,name)
newName from a1) t2,
(select BCode,BNAME,count(Bname)
over(partition by BNAME order by BCode,Bname)
newBName from b1) t3
where t1.CODE=t2.code
and t2.name=t3.bname
and t2.newname=t3.newbname);COMMIT;SQL> SELECT * FROM A1;CODE NAME
---------- ----------
0001 S1
0002 S2
0003 A2