Update ico a set a.bh=(select a.bh||b.myta.bh from myta b) where a.bh=b.bh;
Update ico set bh=bh||(SELECT myta.bh from myta where ico.bh=myta.bh )而且必须保证(SELECT myta.bh from myta where ico.bh=myta.bh )返回的数据条数不能大于1
这是sql server的语法,另外,1楼的语句可以执行吗?别名b的作用域应该只在括号里面,它还能跳到括号外面去?! 个人感觉应该这么写的: Update ico a set a.bh=(select a.bh||b.myta.bh from myta b) where exist (select 1 from myta b where a.bh=b.bh); 10g以上的版本可以考虑使用merge ...using ...
create table ico(bh varchar2(20)) create table myta(bh varchar2(10))insert into ico values('bb'); insert into ico values('cc');insert into myta values('123bb'); insert into myta values('122cc');insert into myta values('bb'); insert into myta values('cc');update ico a set bh=bh||(select b.bh from myta b where a.bh=b.bh)select * from ico
要保证:select b.bh from myta b where a.bh=b.bh唯一
必须保证(SELECT myta.bh from myta where ico.bh=myta.bh )返回的数据条数不能大于1
Update ico set bh=bh||bh from ico where exists (select * from myta where ico.bh=myta.bh)
你好 zxf_feng ,你的这两种方法都不行呀~~~~~
报的什么错,确认一下myta表的bh字段是不是unique key,不是就加上。
SQL问题,主键非空问题,系统保留字问题,检查一下
update ico a set bh=bh||(select b.bh from myta b where a.bh=b.bh) Update ico set bh=bh||(SELECT myta.bh from myta where ico.bh=myta.bh ) 这两个都用过了,都提示缺少表达式,急急!有谁帮解决一下呀
回inthirties,我用的是以下两个语句,可结果都是“缺少表达式” Update ico set bh=bh||(SELECT myta.bh from myta where ico.bh=myta.bh )update ico a set bh=bh||(select b.bh from myta b where a.bh=b.bh)
bh=bh||(SELECT myta.bh from myta where ico.bh=myta.bh ),这个赋值怎么看起来有点怪怪的!
可能有哪个表的bh有重复的吧,用游标一行行update declare vbh ico.bh%Type; cursor cur1 is select bh from myta; begin open cur1; loop; fetch cur1 into vbh; exit when cur1%notFound; update ico set ico.bh = ico.bh||vbh where ico.bh = vbh; end loop; close cur1; end;
这两句会报 缺少表达式的错误要报应该报 单行子查询返回多个行这样的错哟。你是用的什么数据库,这两句需要加上;号,分开执行哟比如 SQL> update ico a set bh=bh||(select b.bh from myta b where a.bh=b.bh);SQL> Update ico 2 set bh=bh||(SELECT myta.bh 3 from myta 4 where ico.bh=myta.bh ) ;这两句要保证成功的话myta 的 bh应该是unique的。
update ico a set bh=bh||(select b.bh from myta b where a.bh=b.bh and rownum < 2);
这种写法是最标准的: Update ico set bh=(SELECT bh||(myta.bh from myta where ico.bh=myta.bh )
Update ico a set a.bh=(select a.bh||b.myta.bh from myta b) where a.bh=b.bh;
set a.bh=(select a.bh||b.myta.bh
from myta b)
where a.bh=b.bh;
set bh=bh||(SELECT myta.bh
from myta
where ico.bh=myta.bh )而且必须保证(SELECT myta.bh
from myta
where ico.bh=myta.bh )返回的数据条数不能大于1
个人感觉应该这么写的:
Update ico a
set a.bh=(select a.bh||b.myta.bh
from myta b)
where exist (select 1 from myta b where a.bh=b.bh);
10g以上的版本可以考虑使用merge ...using ...
create table myta(bh varchar2(10))insert into ico values('bb');
insert into ico values('cc');insert into myta values('123bb');
insert into myta values('122cc');insert into myta values('bb');
insert into myta values('cc');update ico a set bh=bh||(select b.bh from myta b where a.bh=b.bh)select * from ico
必须保证(SELECT myta.bh
from myta
where ico.bh=myta.bh )返回的数据条数不能大于1
set bh=bh||bh
from ico
where exists (select * from myta
where ico.bh=myta.bh)
你好 zxf_feng ,你的这两种方法都不行呀~~~~~
Update ico
set bh=bh||(SELECT myta.bh
from myta
where ico.bh=myta.bh )
这两个都用过了,都提示缺少表达式,急急!有谁帮解决一下呀
名称 是否为空? 类型
----------------------------------------- -------- ---------------
XH VARCHAR2(20)
BH NUMBER(38)SQL> alter table tt_01
2 add constraint pk_tt_01 primary key(xh);表已更改。
回inthirties,我用的是以下两个语句,可结果都是“缺少表达式”
Update ico
set bh=bh||(SELECT myta.bh
from myta
where ico.bh=myta.bh )update ico a set bh=bh||(select b.bh from myta b where a.bh=b.bh)
from myta
where ico.bh=myta.bh ),这个赋值怎么看起来有点怪怪的!
declare
vbh ico.bh%Type;
cursor cur1 is
select bh
from myta;
begin
open cur1;
loop;
fetch cur1 into vbh;
exit when cur1%notFound;
update ico set ico.bh = ico.bh||vbh where ico.bh = vbh;
end loop;
close cur1;
end;
这两句会报 缺少表达式的错误要报应该报 单行子查询返回多个行这样的错哟。你是用的什么数据库,这两句需要加上;号,分开执行哟比如
SQL> update ico a set bh=bh||(select b.bh from myta b where a.bh=b.bh);SQL> Update ico
2 set bh=bh||(SELECT myta.bh
3 from myta
4 where ico.bh=myta.bh ) ;这两句要保证成功的话myta 的 bh应该是unique的。
Update ico
set bh=(SELECT bh||(myta.bh
from myta
where ico.bh=myta.bh )
set a.bh=(select a.bh||b.myta.bh
from myta b)
where a.bh=b.bh;