一、需求背景:
表A:CREATE TABLE T_SMCOMMONMSG (
FXH NUMBER,
FTYPE NUMBER,
FMSG_CONTENT VARCHAR2(1024 BYTE)
)
表B:CREATE TABLE T_SMYL_COMMONSND (
FXH NUMBER,
FMSG_CONTENT1 VARCHAR2(1024 BYTE),
FMSG_CONTENT2 VARCHAR2(1024 BYTE)
)
表A中的原始记录如下:
FXH FTYPE FMSG_CONTENT
------ -------- --------------
98 0 msg1
99 0 msg2
100 0 msg3
101 0 msg4
表B中的现有记录如下:
FXH FMSG_CONTENT1 FMSG_CONTENT2
------- ---------------- --------------
1 空 空
2 空 空
需求:现要求将表A中的记录转换成如下格式的表B中的记录:
FXH FMSG_CONTENT1 FMSG_CONTENT2
------ ---------------- --------------
1 msg1 msg2
2 msg3 msg4
注:表A是一个包含了多种FTYPE类型的信息表,其中FTYPE=0的信息就是转换的原始信息,序号从98开始
表B是一个包含了一种FTYPE类型的信息表,其序号从1开始
二、解决方案:
1.尝试使用如下SQL语句:
update t_smyl_commonsnd a
set a.fmsg_content1 = (select fmsg_content
from t_smcommonmsg b
where b.ftype = 0
and mod(b.fxh,2) =0)
错误原因:子查询的结果是一个结果集,不能将结果集赋給一条记录的某个字段
2.尝试使用如下SQL语句:
update t_smyl_commonsnd a
set a.fmsg_content1 = (select fmsg_content
from t_smcommonmsg b
where b.ftype = 0
and mod(b.fxh,2) =0
and a.fxh = b.fxh -97)
但执行: select * from t_smyl_commonsnd;结果为:
FXH FMSG_CONTENT1 FMSG_CONTEN2
-------- --------------- --------------
1 msg1
2
3 msg2
4
5 msg3
错误原因:子查询中对与表A中FXH值为偶数的记录,其返回值为NULL,所以偶数行的字段都为空
3.尝试使用如下SQL语句:
--更新字段:FMSG_CONTENT1
update t_smyl_commonsnd a
set a.fmsg_content1 = (select fmsg_content
from (select rownum id, fmsg_content
from (select fmsg_content
from t_smcommonmsg
where ftype = 0
and mod(fxh, 2) = 0
order by fxh asc) v1
) v2
Where a.fxh = v2.id);
--更新字段:FMSG_CONTENT2
update t_smyl_commonsnd a
set a.fmsg_content2 = (select fmsg_content
from (select rownum id, fmsg_content
from (select fmsg_content
from t_smcommonmsg
where ftype = 0
and mod(fxh, 2) = 1
order by fxh asc) v1
) v2
where a.fxh = v2.id);
返回结果如下:
FXH FMSG_CONTENT1 FMSG_CONTENT2
----- --------------- ---------------
1 msg1 msg2
2 msg3 msg4
更新成功!
分析:
1)。 找出表A中所有FTYPE=0且FXH为双数的记录,也即是FMSG_CONTENT1字段的目标值
select fmsg_content
from t_smcommonmsg
where ftype = 0
and mod(fxh, 2) = 0
order by fxh asc
2)。給筛选的结果加上Rownum,和表B中的记录一一对应
select rownum id, fmsg_content
from (select fmsg_content
from t_smcommonmsg
where ftype = 0
and mod(fxh, 2) = 0
order by fxh asc) v1
3)。找出视图V1中ID值和表B的FXH号值对应的记录
select fmsg_content
from (select rownum id, fmsg_content
from (select fmsg_content
from t_smcommonmsg
where ftype = 0
and mod(fxh, 2) = 0
order by fxh asc) v1
) v2
where a.fxh = v2.id
4)。更新记录的值:
update t_smyl_commonsnd a
set a.fmsg_content1 = (select fmsg_content
from (select rownum id, fmsg_content
from (select fmsg_content
from t_smcommonmsg
where ftype = 0
and mod(fxh, 2) = 0
order by fxh asc) v1
) v2
where a.fxh = v2.id);
Trackback: http://tb.blog.csdn.net/TrackBack.ASPx?PostId=1476118