A表
create table CkQD
(Id int auto_increment not null,
constraint Id_pr primary key(Id),
Pn varchar(30),
Gz varchar(20),
Sl bigint,
Jz decimal(10,2),
Dw varchar(10),
Ckt datetime,
Jlr varchar(20),
chr varchar(20),
Bz varchar(100))
B表
create table Ck
(Id int auto_increment not null,
constraint Id_pr primary key(Id),
Pn varchar(30),
Gz varchar(20),
Sl bigint,
Jz decimal(10,2),
Dw varchar(10),
Ckt datetime,
Jlr varchar(20),
chr varchar(20),
Bz varchar(100))现在我要把每次A表内容插入B表,但是B表的ID列必须用自动生成的,不能插入A表的进来,我的语句出现问题不能执行
insert into ck(ck.id(default),(select ckqd.Pn,ckqd.Gz,ckqd.Sl,ckqd.Jz,ckqd.Dw,ckqd.Ckt,ckqd.Jlr,ckqd.chr,ckqd.Bz from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl));求解决方法
create table CkQD
(Id int auto_increment not null,
constraint Id_pr primary key(Id),
Pn varchar(30),
Gz varchar(20),
Sl bigint,
Jz decimal(10,2),
Dw varchar(10),
Ckt datetime,
Jlr varchar(20),
chr varchar(20),
Bz varchar(100))
B表
create table Ck
(Id int auto_increment not null,
constraint Id_pr primary key(Id),
Pn varchar(30),
Gz varchar(20),
Sl bigint,
Jz decimal(10,2),
Dw varchar(10),
Ckt datetime,
Jlr varchar(20),
chr varchar(20),
Bz varchar(100))现在我要把每次A表内容插入B表,但是B表的ID列必须用自动生成的,不能插入A表的进来,我的语句出现问题不能执行
insert into ck(ck.id(default),(select ckqd.Pn,ckqd.Gz,ckqd.Sl,ckqd.Jz,ckqd.Dw,ckqd.Ckt,ckqd.Jlr,ckqd.chr,ckqd.Bz from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl));求解决方法
select ckqd.Pn,ckqd.Gz,ckqd.Sl,ckqd.Jz,ckqd.Dw,ckqd.Ckt,ckqd.Jlr,ckqd.chr,ckqd.Bz from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl;
Procedure execution failed
1172 - Result consisted of more than one row
我的过程如下
BEGINDECLARE v_CkQD_Pn varchar(30);
declare v_CkQD_Sl bigint;
declare stopFlag int;
declare v_Rk_Sl bigint;
DECLARE cursor_ckqd CURSOR
FOR select Pn,Sl
from CkQD;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1; insert into ck(Pn , Gz , Sl , Jz, Dw , Ckt , Jlr , chr , Bz )
select ckqd.Pn,ckqd.Gz,ckqd.Sl,ckqd.Jz,ckqd.Dw,ckqd.Ckt,ckqd.Jlr,ckqd.chr,ckqd.Bz from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl;OPEN cursor_ckqd;
REPEAT
FETCH cursor_ckqd INTO v_CkQD_Pn,v_CkQD_Sl;
begin
if (stopFlag is null) then
select Sl into v_Rk_Sl from Rk where Pn = v_CkQD_Pn;
if (v_CkQD_Sl <= v_Rk_Sl) then
delete from CkQD where pn = v_CkQD_Pn;
update Rk set Sl = v_Rk_Sl - v_CkQD_Sl where pn = v_CkQD_Pn; end if;
end if;
end;
UNTIL stopFlag = 1 END REPEAT;
CLOSE cursor_ckqd;
END
检查结果是否有多条
1172 - Result consisted of more than one row,就回滚,分不够再加
declare exit handler for sqlexception rollback;
declare exit handler for sqlwarning rollback;
START TRANSACTION;
SQL语句
COMMIT;