当然可以了
update a set col1=(select col1 from b where a.id=b.id)
where exists (select 'X' from b where a.id=b.id);
update a set col1=(select col1 from b where a.id=b.id)
where exists (select 'X' from b where a.id=b.id);
update a set col=(select col from b where a.id=b.id)
where id in (select id from b where a.id=b.id);
例如字段名是col1,col2,col3等怎么实现呢?
是不是
update a set col1,col2,col3=(select col1,col2,col3 from b where a.id=b.id)
where exists (select 'X' from b where a.id=b.id);
这样好象不太妥当吧?
在线等!
update a set (col1,col2,col3)=(select col1,col2,col3 from b where a.id=b.id)
where exists (select 'X' from b where a.id=b.id);
我试试看,顺便问一下
那个exists (select 'X' from b where a.id=b.id);中'X'是不是表中的存在的字段名就行了?
exists (select 1 from b where a.id=b.id);与
exists (select 'X' from b where a.id=b.id);等价
ORA-00904: 无效列名???我的列名存在千真万确!我的格式是这样的:
update a set (col1,col2,col3)=(select col1,col2,col3 from b where b.id1=ss and a.id=b.id)
where exists (select 'X' from b where a.id=b.id);这里a.id和b.id的id只要是a,b中都有的字段就可以了吧?
为什么不行呢?
update a set (col1,col2,col3)=(select col1,col2,col3 from b where b.id=ss and a.id=b.id) where exists (select 'X' from b where a.id=b.id);
吧,你写成了b.id1.
好象在oracle里面没有批量更新,批量删除也是这样吧,条件也是等于别人的ID,所以我准备用循环,一个一个来,不过这样效率就太低了,不知道各位有没有什么办法?
update a set
(col1,
col2,
col3)=
(select col1,col2,col3 from b where b.id1=ss and a.id=b.id)
where exists (select 'X' from b where a.id=b.id);应该是你的字段名写错了