现有两个表 表1: S1 字段有a,b,c,d a为主键
表2: s2 字段有a, c,d,e,f a为主键
表S1中的字段都有值,现需要把S1表中的c,d字段的值般到S2表中来,他们主键a是相等的。
由于有几千条数据,所以手动更新不现实,现求高手给一条能完成此项功能的SQL语句
数据库用的是oracle10g,先谢谢了
表2: s2 字段有a, c,d,e,f a为主键
表S1中的字段都有值,现需要把S1表中的c,d字段的值般到S2表中来,他们主键a是相等的。
由于有几千条数据,所以手动更新不现实,现求高手给一条能完成此项功能的SQL语句
数据库用的是oracle10g,先谢谢了
解决方案 »
- oracle11g ORA-01034:oracle not available ORA-27101:shared memory realm does not
- 如何更改oracle的实例名
- 关于98操作下oracle跨网段的访问
- java.sql.SQLException: Listener refused the connection with the following error:
- ORACLE中如何实现的累计列?
- 初学!那位大虾介绍一些书来参考一下!
- 遇到一个错误
- 初学者的问题,请高手指点!建库出错!
- 求助高人:如何在PHP4.2.3里连接Oracle8.1.6?
- 游标报错 请大家帮忙看下
- Oracle 独占锁(exclusive lock)允许其它事务读取数据吗?
- 我用update更新一表,怎么将更新的表中几个字段同时搬入另一表
uodate s2
set c=(select a.c from s1 a,s2 b where a.a=b.a),
d=(select a.d from s1 a,s2 b where a.a=b.a)
(c,d)=(select c,d from s1 where s1.a=s2.a)
where exists(select 'Z' from s1 where s1.a=s2.a)
如果s2没数据的话,就可以用insert就简单
insert into s2
(a,c,d)
select a,c,d from s1
set (c,d)=
(select c,d from s1 a
where a.a=b.a)
where exists
(
select 1 from s1 a
where a.a=b.a
)
你们试了没?
你们的方法好象都不行的啊
我换把你们的思路转成我实际的表的语句
1楼的
update sb_sjqc
set jzh=(select a.jzh from sbdhxb a,sb_sjqc b where a.sbbm=b.sbbm),
zxh=(select a.zxh from sbdhxb a,sb_sjqc b where a.sbbm=b.sbbm),
th=(select a.th from sbdhxb a,sb_sjqc b where a.sbbm=b.sbbm),
fzwz=(select a.fzwz from sbdhxb a,sb_sjqc b where a.sbbm=b.sbbm)
2,3楼的
update sb_sjqc set (jzh,zxh,th,fzwz)=(select jzh,zxh,th,fzwz from sbdhxb where sbdhxb.sbbm=sb_sjqc.sbbm)
where exists(select 'z' from sbdhxb where sbdhxb.sbbm=sb_sjqc.sbbm)结果都是错误的:ORA-01427:单行子查询返回多个行
返回的列是N多行,几千行的
我把我具体的表模型及数据写下来
表s1:
字 段: a, b, c, d
数据1:01 b1 c1 d1
数据2:02 b2 c2 d2
数据3:03 b3 c3 d3表s2:
字 段: a, c, d, e, f
数据1:01 e1 f1
数据2:02 e2 f2
数据3:03 e3 f3期望运行SQL语句后表s2为
表s2:
字 段: a, c, d, e, f
数据1:01 c1 d1 e1 f1
数据2:02 c2 d2 e2 f2
数据3:03 c3 d3 e3 f3
update s2
(c,d)=(select c,d from s1 where s1.a=s2.a)
where exists(select 'Z' from s1 where s1.a=s2.a)其中(select c,d from s1 where s1.a=s2.a)这句话确实报错,单行子查询返回多行值
我把我问题再说得更详细一点
就是s1表在增加数据的时候自动由存储过程把s1表的值带到s2表中去
但是开始s2表中没有c,d字段,后来由于业务需求,需要新增c,d字段,但是以前从s1表表中带进去的那些数据c,d字段都没值了
本来就是正确的,我也测试过了,你也可以试下.
下面是测试数据:create table s1
(a varchar(10),b varchar(10),c varchar(10),d varchar(10),
constraint PK_s1 primary key (a))
;create table s2
(a varchar(10),b varchar(10),c varchar(10),d varchar(10),e varchar(10),f varchar(10),
constraint PK_s2 primary key (a))
;
insert into s1
(a,b,c,d)
select '01','b1','c1','d1' from dual union
select '02','b2','c2','d2' from dual union
select '03','b3','c3','d3' from dual
;commit;
insert into s2
(a,e,f)
select '01','e1','f1' from dual union
select '02','e2','f2' from dual union
select '03','e3','f3' from dual
;commit;
update s2
set (c,d)=(select c,d from s1 where s1.a=s2.a)
where exists(select 'Z' from s1 where s1.a=s2.a)
;commit;再说另个问题:
单行子查询返回多行值
说明相同的a有多条记录(a也不会是主健的),数据有问题,你可以用下面的sql找出来select s1.a,count(*) from s1,s2 where s1.a=s2.a group by s1.a having count(*)>1;这样的数据和你的描述是不一致的.有问题再说
我用你那句SQL测试了一下,确实S2每行都有两个值。有办法搞吗?-- Create table 相当于S1
create table SB_CKDXB
(
PID VARCHAR2(18) not null,
BH VARCHAR2(17) not null,
LSH VARCHAR2(20) not null,
SBBH VARCHAR2(20),
SBMC VARCHAR2(60),
DW VARCHAR2(50),
SL NUMBER(10),
CKCK VARCHAR2(50),
BZ VARCHAR2(1000),
CKDJ NUMBER(13,2),
AZWZ VARCHAR2(100),
RKDBH VARCHAR2(17) not null,
DHPH VARCHAR2(60),
SBGG VARCHAR2(400),
GRYJ NUMBER(13,2),
FTFY NUMBER(13,2),
ZJ NUMBER(13,2),
SCCJ VARCHAR2(100),
TH VARCHAR2(60),
BJH VARCHAR2(60),
JZH VARCHAR2(30),
ZXH VARCHAR2(30),
FZWZ VARCHAR2(30),
RKDLSH VARCHAR2(20) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table SB_CKDXB
is '设备出库细表';
-- Add comments to the columns
comment on column SB_CKDXB.PID
is '工程项目编号';
comment on column SB_CKDXB.BH
is '主表编号';
comment on column SB_CKDXB.LSH
is '编号';
comment on column SB_CKDXB.SBBH
is '设备编号';
comment on column SB_CKDXB.SBMC
is '设备名称';
comment on column SB_CKDXB.DW
is '单位';
comment on column SB_CKDXB.SL
is '数量';
comment on column SB_CKDXB.CKCK
is '出库仓库';
comment on column SB_CKDXB.BZ
is '备注';
comment on column SB_CKDXB.CKDJ
is '单价';
comment on column SB_CKDXB.AZWZ
is '安装位置';
comment on column SB_CKDXB.RKDBH
is '入库单编号';
comment on column SB_CKDXB.DHPH
is '到货批号';
comment on column SB_CKDXB.SBGG
is '规格型号';
comment on column SB_CKDXB.GRYJ
is '总价';
comment on column SB_CKDXB.FTFY
is '分摊费用';
comment on column SB_CKDXB.ZJ
is '合计价值(总价+分摊费用)';
comment on column SB_CKDXB.SCCJ
is '生产厂家/牌号';
comment on column SB_CKDXB.JZH
is '机组号';
comment on column SB_CKDXB.ZXH
is '装箱号';
comment on column SB_CKDXB.FZWZ
is '放置位置';
comment on column SB_CKDXB.RKDLSH
is '入库单流水号1';
-- Create/Recreate primary, unique and foreign key constraints
alter table SB_CKDXB
add constraint PK_SB_CKDXB primary key (PID, LSH, BH, RKDLSH, RKDBH)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create unique index PK_SBCKXB on SB_CKDXB (PID, BH, LSH, RKDBH, RKDLSH)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table 相当于S2
create table SB_SJQC
(
PID VARCHAR2(18) not null,
SBBM VARCHAR2(40) not null,
SB_PROPERTY NUMBER(1) not null,
PM VARCHAR2(100),
EPM VARCHAR2(80),
GG VARCHAR2(400),
HTH VARCHAR2(17),
HTJE NUMBER(18,2),
PZFK NUMBER(15,2),
CWFK NUMBER(15,2),
YZF NUMBER(13,2),
FUN_NAME VARCHAR2(60),
SBGCBH VARCHAR2(20),
AZDW VARCHAR2(60),
AZBW VARCHAR2(60),
AZRQ DATE,
GDZCBM VARCHAR2(30),
JHDCBRQ DATE,
JHDXCRQ DATE,
SJDCBRQ DATE,
SJDXCRQ DATE,
ACTIVEID VARCHAR2(20),
TH VARCHAR2(20),
LVL NUMBER(3),
DW VARCHAR2(40),
KEY_STATE NUMBER(1) default 0,
SL NUMBER(13,2),
SB_TYPE VARCHAR2(40),
SB_SCCJ VARCHAR2(100),
SB_SJDH VARCHAR2(100),
SB_ZZRQ DATE,
SB_TYSJ DATE,
SB_SYSM VARCHAR2(20),
SB_JSGF VARCHAR2(2000),
REPAIR_DEPART VARCHAR2(100),
REPAIR_GROUP VARCHAR2(100),
REPAIR_PERSON VARCHAR2(20),
REPAIR_TURN NUMBER(12,2),
JZH VARCHAR2(30),
ZXH VARCHAR2(60),
FZWZ VARCHAR2(30),
SCCS VARCHAR2(60)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table SB_SJQC
is '设备清册';
-- Add comments to the columns
comment on column SB_SJQC.PID
is '工程项目编号';
comment on column SB_SJQC.SBBM
is '设备编码';
comment on column SB_SJQC.SB_PROPERTY
is '属性';
comment on column SB_SJQC.PM
is '设备名称';
comment on column SB_SJQC.EPM
is '英文名称';
comment on column SB_SJQC.GG
is '规格型号';
comment on column SB_SJQC.HTH
is '合同编号(内部流水号)';
comment on column SB_SJQC.HTJE
is '合同金额';
comment on column SB_SJQC.PZFK
is '批准付款';
comment on column SB_SJQC.CWFK
is '财务付款';
comment on column SB_SJQC.YZF
is '运杂费';
comment on column SB_SJQC.FUN_NAME
is '岛号';
comment on column SB_SJQC.SBGCBH
is '设备概算项目';
comment on column SB_SJQC.AZDW
is '安装单位';
comment on column SB_SJQC.AZBW
is '安装部位';
comment on column SB_SJQC.AZRQ
is '安装就位日期';
comment on column SB_SJQC.GDZCBM
is '固定资产编码';
comment on column SB_SJQC.JHDCBRQ
is '计划到车板日期';
comment on column SB_SJQC.JHDXCRQ
is '计划到现场日期';
comment on column SB_SJQC.SJDCBRQ
is '实际到车板日期';
comment on column SB_SJQC.SJDXCRQ
is '实际到现场日期';
comment on column SB_SJQC.ACTIVEID
is 'P3作业代码';
comment on column SB_SJQC.TH
is '图号';
comment on column SB_SJQC.LVL
is '层次';
comment on column SB_SJQC.KEY_STATE
is '是否是关键作业';
comment on column SB_SJQC.SL
is '数量';
comment on column SB_SJQC.JZH
is '机组号';
comment on column SB_SJQC.ZXH
is '装箱号';
comment on column SB_SJQC.FZWZ
is '放置位置';
comment on column SB_SJQC.SCCS
is '生产厂商';
-- Create/Recreate primary, unique and foreign key constraints
alter table SB_SJQC
add constraint PK_SB_SJQC primary key (PID, SBBM)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
我用你那句SQL测试了一下,确实S2每行都有两个值。有办法搞吗? -- Create table 相当于S1 -- Create table
create table SBDHXB
(
PID VARCHAR2(18) not null,
PH VARCHAR2(60) not null,
HTH VARCHAR2(17) not null,
GCBH VARCHAR2(15) not null,
SBBM VARCHAR2(40) not null,
MANUFACTURER VARCHAR2(60),
BZ VARCHAR2(1000),
JS NUMBER(13,3),
PM VARCHAR2(100),
GG VARCHAR2(400),
DJ NUMBER(14,2),
ZJ NUMBER(14,2),
ZCBH VARCHAR2(50),
DW VARCHAR2(40),
LSSBBM VARCHAR2(20),
TH VARCHAR2(60),
FHJS NUMBER(6),
BLJS NUMBER(6),
JZH VARCHAR2(30),
ZXH VARCHAR2(60),
FZWZ VARCHAR2(30),
SBLX VARCHAR2(30)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table SBDHXB
is '设备到货细表';
-- Add comments to the columns
comment on column SBDHXB.PID
is '工程项目编号';
comment on column SBDHXB.PH
is '批号';
comment on column SBDHXB.HTH
is '合同编号(内部流水号)';
comment on column SBDHXB.GCBH
is '工程编号';
comment on column SBDHXB.SBBM
is '设备编码';
comment on column SBDHXB.MANUFACTURER
is '生产厂商';
comment on column SBDHXB.BZ
is '备注';
comment on column SBDHXB.JS
is '件数';
comment on column SBDHXB.PM
is '品名';
comment on column SBDHXB.GG
is '规格';
comment on column SBDHXB.DJ
is '设备单价';
comment on column SBDHXB.ZJ
is '设备分摊价';
comment on column SBDHXB.ZCBH
is '资产编号';
comment on column SBDHXB.DW
is '单位';
comment on column SBDHXB.LSSBBM
is '设备合同供货范围(隶属设备编码)';
comment on column SBDHXB.TH
is '图号';
comment on column SBDHXB.FHJS
is '发货单数量';
comment on column SBDHXB.BLJS
is '补录数量';
comment on column SBDHXB.JZH
is '机组号';
comment on column SBDHXB.ZXH
is '装箱号';
comment on column SBDHXB.FZWZ
is '放置位置';
comment on column SBDHXB.SBLX
is '设备类型';
-- Create/Recreate primary, unique and foreign key constraints
alter table SBDHXB
add constraint PK_SBDHXB primary key (PID, PH, HTH, GCBH, SBBM)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table SBDHXB
add constraint FK_SBDHXB_REFERENCE_HTSBQD foreign key (PID, HTH, GCBH, LSSBBM)
references HTSBQD (PID, HTH, GCBH, BM);
alter table SBDHXB
add constraint FK_SBDHXB_REFERENCE_SBDHPB foreign key (PID, PH, HTH)
references SBDHPB (PID, PH, HTH) on delete cascade;
-- Create/Recreate indexes
create unique index INX_SBDHXB on SBDHXB (PH, GCBH, SBBM)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table 相当于S2
create table SB_SJQC
(
PID VARCHAR2(18) not null,
SBBM VARCHAR2(40) not null,
SB_PROPERTY NUMBER(1) not null,
PM VARCHAR2(100),
EPM VARCHAR2(80),
GG VARCHAR2(400),
HTH VARCHAR2(17),
HTJE NUMBER(18,2),
PZFK NUMBER(15,2),
CWFK NUMBER(15,2),
YZF NUMBER(13,2),
FUN_NAME VARCHAR2(60),
SBGCBH VARCHAR2(20),
AZDW VARCHAR2(60),
AZBW VARCHAR2(60),
AZRQ DATE,
GDZCBM VARCHAR2(30),
JHDCBRQ DATE,
JHDXCRQ DATE,
SJDCBRQ DATE,
SJDXCRQ DATE,
ACTIVEID VARCHAR2(20),
TH VARCHAR2(20),
LVL NUMBER(3),
DW VARCHAR2(40),
KEY_STATE NUMBER(1) default 0,
SL NUMBER(13,2),
SB_TYPE VARCHAR2(40),
SB_SCCJ VARCHAR2(100),
SB_SJDH VARCHAR2(100),
SB_ZZRQ DATE,
SB_TYSJ DATE,
SB_SYSM VARCHAR2(20),
SB_JSGF VARCHAR2(2000),
REPAIR_DEPART VARCHAR2(100),
REPAIR_GROUP VARCHAR2(100),
REPAIR_PERSON VARCHAR2(20),
REPAIR_TURN NUMBER(12,2),
JZH VARCHAR2(30),
ZXH VARCHAR2(60),
FZWZ VARCHAR2(30),
SCCS VARCHAR2(60)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table SB_SJQC
is '设备清册';
-- Add comments to the columns
comment on column SB_SJQC.PID
is '工程项目编号';
comment on column SB_SJQC.SBBM
is '设备编码';
comment on column SB_SJQC.SB_PROPERTY
is '属性';
comment on column SB_SJQC.PM
is '设备名称';
comment on column SB_SJQC.EPM
is '英文名称';
comment on column SB_SJQC.GG
is '规格型号';
comment on column SB_SJQC.HTH
is '合同编号(内部流水号)';
comment on column SB_SJQC.HTJE
is '合同金额';
comment on column SB_SJQC.PZFK
is '批准付款';
comment on column SB_SJQC.CWFK
is '财务付款';
comment on column SB_SJQC.YZF
is '运杂费';
comment on column SB_SJQC.FUN_NAME
is '岛号';
comment on column SB_SJQC.SBGCBH
is '设备概算项目';
comment on column SB_SJQC.AZDW
is '安装单位';
comment on column SB_SJQC.AZBW
is '安装部位';
comment on column SB_SJQC.AZRQ
is '安装就位日期';
comment on column SB_SJQC.GDZCBM
is '固定资产编码';
comment on column SB_SJQC.JHDCBRQ
is '计划到车板日期';
comment on column SB_SJQC.JHDXCRQ
is '计划到现场日期';
comment on column SB_SJQC.SJDCBRQ
is '实际到车板日期';
comment on column SB_SJQC.SJDXCRQ
is '实际到现场日期';
comment on column SB_SJQC.ACTIVEID
is 'P3作业代码';
comment on column SB_SJQC.TH
is '图号';
comment on column SB_SJQC.LVL
is '层次';
comment on column SB_SJQC.KEY_STATE
is '是否是关键作业';
comment on column SB_SJQC.SL
is '数量';
comment on column SB_SJQC.JZH
is '机组号';
comment on column SB_SJQC.ZXH
is '装箱号';
comment on column SB_SJQC.FZWZ
is '放置位置';
comment on column SB_SJQC.SCCS
is '生产厂商';
-- Create/Recreate primary, unique and foreign key constraints
alter table SB_SJQC
add constraint PK_SB_SJQC primary key (PID, SBBM)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
如果不可以的话,需要确定s1中的1条记录对应s2中的多条记录的时候,确定去哪条s2中的记录来更新.
可以随便取一条的话,可以修改原语句中
(select c,d from s1 where s1.a=s2.a)只有1条记录就可
为,简单修改如下:
(select c,d from s1 where s1.a=s2.a and rownum=1)