用了鱼的方法,好像并不支持 update set from....会报错: PLS-00103: Encountered the symbol "FROM" when expecting one of the following: . ( , * @ % & - + ; / at mod rem return returning <an exponent (**)> where ||
try:Update A Set A2=B.B2 From A Inner Join B On A.A1=B.B1 => update a set a2= (select top 1 b2 from b where b.b1=a.a1)
樓主什麼數據庫??不行就用子查詢試下Update A Set A2=(Select B2 From B Where B1=A.A1) Where A1 In (Select B1 From B)後面的條件要加上。
declare @bkey nvarchar(50) declare @bdata nvarchar(50)DECLARE cs CURSOR FOR select b1,b2 from bopen cs fetch next from cs into @bkey,@bdatawhile @@fetch_status =0 begin update a set a2=@bdata where a1=@bkey fetch next from cs into @bkey,@bdata endclose cs DEALLOCATE cs
最后还是用了循环: = =!!declare type rowtable is table of varchar2(40); type chartable is table of varchar2(128); rowids rowtable; value1s chartable;begin select a.rowid,b.B2 bulk collect into rowids, value1s from A a, B b where a.A1=b.B1; forall this in rowids.first..rowids.last update coruser set A2 = value1s(this) where rowid = chartorowid(rowids(this)); end;
你这样会不会出现两条记录?A1 A2,A1 B2??
Update A Set A2=B.B2 From A Inner Join B On A.A1=B.B1OrUpdate A Set A2=B.B2 From A,B Where A.A1=B.B1
A1不可重复,A2本身有数据,是要把B2的数据覆盖相应的A2
你这样会不会出现两条记录?A1 A2,A1 B2??
----------------
用我的語句做個更新即可
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
. ( , * @ % & - + ; / at mod rem return returning
<an exponent (**)> where ||
=>
update a set a2=
(select top 1 b2 from b where b.b1=a.a1)
Where A1 In (Select B1 From B)後面的條件要加上。
declare @bdata nvarchar(50)DECLARE cs CURSOR FOR
select b1,b2 from bopen cs
fetch next from cs into @bkey,@bdatawhile @@fetch_status =0 begin
update a set a2=@bdata where a1=@bkey
fetch next from cs into @bkey,@bdata
endclose cs
DEALLOCATE cs
type rowtable is table of varchar2(40);
type chartable is table of varchar2(128);
rowids rowtable;
value1s chartable;begin
select a.rowid,b.B2 bulk collect into rowids, value1s
from A a, B b
where a.A1=b.B1;
forall this in rowids.first..rowids.last
update coruser set A2 = value1s(this)
where rowid = chartorowid(rowids(this));
end;