为什么这个语句在ORACLE中不能执行? update itmmaster SET itmmaster.SEAKEY_0=B.OLD4 from itmmaster A,ztemp B where A.ITMREF_0=B.NEWCODE 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 上述语句在SQL中执行是正常的。 update itmmaster SET A.SEAKEY_0=B.OLD4 from itmmaster A,ztemp B where A.ITMREF_0=B.NEWCODE把itmmaster换成别名A啊,试试吧 update itmmaster ASET SEAKEY_0= (select B.OLD4 from ztemp B where A.ITMREF_0=B.NEWCODE) 我试了把itmmaster换成别名A,不行。使用gxlineji提供的方法,提供错误“单行子查询返回多个行”错误。 子查询里用max()取最大值试下 两个表的创建语句-- Create table create table ZTEMP ( NEWCODE VARCHAR2(10), NEWNAME VARCHAR2(100), OLDCODE VARCHAR2(10), OLDNAME VARCHAR2(100), OLD4 VARCHAR2(10) ) tablespace GJERP_DAT pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 minextents 1 maxextents unlimited );-- Create table create table ITMMASTER ( ITMREF_0 VARCHAR2(30) not null, TSICOD_0 VARCHAR2(18) not null, TSICOD_1 VARCHAR2(18) not null, TSICOD_2 VARCHAR2(18) not null, TSICOD_3 VARCHAR2(18) not null, TSICOD_4 VARCHAR2(18) not null, TCLCOD_0 VARCHAR2(15) not null, CLSTYP_0 NUMBER(3) not null, SEAKEY_0 VARCHAR2(60) ) tablespace GJERP_DAT pctfree 10 initrans 1 maxtrans 255 storage ( initial 19 minextents 1 maxextents unlimited ); update itmmaster SET A.SEAKEY_0=B.OLD4 from itmmaster A,ztemp B where A.ITMREF_0=B.NEWCODE显示“SQL命令未正确结束”错误。 update itmmaster SET itmmaster.SEAKEY_0=(select ztemp.OLD4 from ztemp where itmmaster.ITMREF_0=ztemp.NEWCODE); drop table ztempcreate table ZTEMP ( NEWCODE VARCHAR2(100), NEWNAME VARCHAR2(100), OLDCODE VARCHAR2(10), OLDNAME VARCHAR2(100), OLD4 VARCHAR2(100) )drop table ITMMASTER create table ITMMASTER ( ITMREF_0 VARCHAR2(100) not null, TSICOD_0 VARCHAR2(18) not null, TSICOD_1 VARCHAR2(18) not null, TSICOD_2 VARCHAR2(18) not null, TSICOD_3 VARCHAR2(18) not null, TSICOD_4 VARCHAR2(18) not null, TCLCOD_0 VARCHAR2(15) not null, CLSTYP_0 NUMBER(3) not null, SEAKEY_0 VARCHAR2(100) ) update itmmaster SET itmmaster.SEAKEY_0=(select ztemp.OLD4 from ztemp where itmmaster.ITMREF_0=ztemp.NEWCODE);--where itmmaster.ITMREF_0=ztemp.NEWCODE drop table ztempcreate table ZTEMP ( NEWCODE VARCHAR2(100), NEWNAME VARCHAR2(100), OLDCODE VARCHAR2(10), OLDNAME VARCHAR2(100), OLD4 VARCHAR2(100) )drop table ITMMASTER create table ITMMASTER ( ITMREF_0 VARCHAR2(100) not null, TSICOD_0 VARCHAR2(18) not null, TSICOD_1 VARCHAR2(18) not null, TSICOD_2 VARCHAR2(18) not null, TSICOD_3 VARCHAR2(18) not null, TSICOD_4 VARCHAR2(18) not null, TCLCOD_0 VARCHAR2(15) not null, CLSTYP_0 NUMBER(3) not null, SEAKEY_0 VARCHAR2(100) ) update itmmaster SET itmmaster.SEAKEY_0=(select ztemp.OLD4 from ztemp where itmmaster.ITMREF_0=ztemp.NEWCODE and rownum=1);--where itmmaster.ITMREF_0=ztemp.NEWCODE 如何知道某个索引需要变成反转键索引 求oracle SQL语法 oracle 批量update 找出不存在的记录 求教一个类似取唯一列的sql语句 对ORACLE数据库的整理和复制。求教!急。. 请叫高手帮我写个SQL查询语句 下面的语句是什么意思? 关于时间的小问题 oracle 9i的数据类型问题 oracle 自动任务 定时执行 小弟不才,拿着本Oracle的英文教材想不开。。 一张表必须且只能占用一个段吗?
from itmmaster A,ztemp B
where A.ITMREF_0=B.NEWCODE把itmmaster换成别名A啊,试试吧
SET SEAKEY_0= (
select B.OLD4
from ztemp B
where A.ITMREF_0=B.NEWCODE
)
-- Create table
create table ZTEMP
(
NEWCODE VARCHAR2(10),
NEWNAME VARCHAR2(100),
OLDCODE VARCHAR2(10),
OLDNAME VARCHAR2(100),
OLD4 VARCHAR2(10)
)
tablespace GJERP_DAT
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);-- Create table
create table ITMMASTER
(
ITMREF_0 VARCHAR2(30) not null,
TSICOD_0 VARCHAR2(18) not null,
TSICOD_1 VARCHAR2(18) not null,
TSICOD_2 VARCHAR2(18) not null,
TSICOD_3 VARCHAR2(18) not null,
TSICOD_4 VARCHAR2(18) not null,
TCLCOD_0 VARCHAR2(15) not null,
CLSTYP_0 NUMBER(3) not null,
SEAKEY_0 VARCHAR2(60)
)
tablespace GJERP_DAT
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 19
minextents 1
maxextents unlimited
);
update itmmaster SET A.SEAKEY_0=B.OLD4
from itmmaster A,ztemp B
where A.ITMREF_0=B.NEWCODE显示“SQL命令未正确结束”错误。
create table ZTEMP
(
NEWCODE VARCHAR2(100),
NEWNAME VARCHAR2(100),
OLDCODE VARCHAR2(10),
OLDNAME VARCHAR2(100),
OLD4 VARCHAR2(100)
)
drop table ITMMASTER
create table ITMMASTER
(
ITMREF_0 VARCHAR2(100) not null,
TSICOD_0 VARCHAR2(18) not null,
TSICOD_1 VARCHAR2(18) not null,
TSICOD_2 VARCHAR2(18) not null,
TSICOD_3 VARCHAR2(18) not null,
TSICOD_4 VARCHAR2(18) not null,
TCLCOD_0 VARCHAR2(15) not null,
CLSTYP_0 NUMBER(3) not null,
SEAKEY_0 VARCHAR2(100)
)
update itmmaster SET itmmaster.SEAKEY_0=(select ztemp.OLD4 from ztemp where itmmaster.ITMREF_0=ztemp.NEWCODE);
--where itmmaster.ITMREF_0=ztemp.NEWCODE
create table ZTEMP
(
NEWCODE VARCHAR2(100),
NEWNAME VARCHAR2(100),
OLDCODE VARCHAR2(10),
OLDNAME VARCHAR2(100),
OLD4 VARCHAR2(100)
)
drop table ITMMASTER
create table ITMMASTER
(
ITMREF_0 VARCHAR2(100) not null,
TSICOD_0 VARCHAR2(18) not null,
TSICOD_1 VARCHAR2(18) not null,
TSICOD_2 VARCHAR2(18) not null,
TSICOD_3 VARCHAR2(18) not null,
TSICOD_4 VARCHAR2(18) not null,
TCLCOD_0 VARCHAR2(15) not null,
CLSTYP_0 NUMBER(3) not null,
SEAKEY_0 VARCHAR2(100)
)
update itmmaster SET itmmaster.SEAKEY_0=(select ztemp.OLD4 from ztemp where itmmaster.ITMREF_0=ztemp.NEWCODE and rownum=1);
--where itmmaster.ITMREF_0=ztemp.NEWCODE