merge into hy_wldw a
using (select * from jh_jk_order where TRANSID in('646','645')) b
on (a.dwno =b.SELLERID)
when matched then
update
set a.dwmc = b.sname,
a.dwlx = '2',
a.dz = b.saddress,
a.cs = decode(b.FROMDISTRICT, null, b.FROMCITY, b.FROMDISTRICT),
a.sf = b.FROMPROV,
a.lxr = b.sname,
a.dh = b.SMOBILEPHONE,
a.sjno = b.SELLERID,
a.yzbm = '1',
a.xtfl = '1'
when not matched then
insert
( dwmc, dwlx, dz, cs, sf, lxr, dh, sjno, yzbm, xtfl)
values
(
b.SNAME,
'2',
b.SADDRESS,
decode(b.FROMDISTRICT, null, b.FROMCITY, b.FROMDISTRICT),
b.FROMPROV,
b.SNAME,
b.SMOBILEPHONE,
b.SELLERID,
'1',
'1')
求找错!~~~~~~~
using (select * from jh_jk_order where TRANSID in('646','645')) b
on (a.dwno =b.SELLERID)
when matched then
update
set a.dwmc = b.sname,
a.dwlx = '2',
a.dz = b.saddress,
a.cs = decode(b.FROMDISTRICT, null, b.FROMCITY, b.FROMDISTRICT),
a.sf = b.FROMPROV,
a.lxr = b.sname,
a.dh = b.SMOBILEPHONE,
a.sjno = b.SELLERID,
a.yzbm = '1',
a.xtfl = '1'
when not matched then
insert
( dwmc, dwlx, dz, cs, sf, lxr, dh, sjno, yzbm, xtfl)
values
(
b.SNAME,
'2',
b.SADDRESS,
decode(b.FROMDISTRICT, null, b.FROMCITY, b.FROMDISTRICT),
b.FROMPROV,
b.SNAME,
b.SMOBILEPHONE,
b.SELLERID,
'1',
'1')
求找错!~~~~~~~
解决方案 »
- merge、delete、/*+ BYPASS_UJVC*/,删除效率
- oracle 提示 oracle not available
- Oracle EM重新配置的奇怪问题
- 大整数读写库
- 一个难题!这里有人能否帮忙解决?
- Oracle的那个表中存放着主机名信息。
- 帮帮忙 linux下安装ORACLE!!
- 时间查询的问题:oracle数据库的时间格式是(2003-5-8 15:40:56),我想查的时间在5-8-2003和5-13-2003之间的数据,该怎么查??
- 为什么会报这个错误》??
- oracle sql developer如何远程连接数据库,如何设置外网跳转
- oracle sqlplus 写的一个存储过程,看看有什么问题
- 数据文件被删除了,启动数据库错误
create table JH_JK_ORDER
(
PROVIDERID VARCHAR2(50) not null,
TRANSID CHAR(40) not null,
SELLERID CHAR(20) not null,
SELLERNAME CHAR(30) not null,
BUYERID CHAR(20) not null,
BUYERNAME CHAR(30) not null,
FLAG CHAR(1),
LOGISTICSID VARCHAR2(100),
AWBID VARCHAR2(100),
SNAME CHAR(50) not null,
STELEPHONE CHAR(50) not null,
SMOBILEPHONE CHAR(50) not null,
SADDRESS VARCHAR2(200) not null,
BNAME CHAR(50) not null,
BTELEPHONE CHAR(50) not null,
BMOBILEPHONE CHAR(50) not null,
BADDRESS VARCHAR2(200) not null,
STMTFORM CHAR(1),
COLLECTION NUMBER(16,2),
CODUSERNAME CHAR(30),
CODBANK CHAR(50),
CODACCOUNT CHAR(80),
TRANSMODE CHAR(1),
FREIGHT NUMBER(16,2),
ORDERDATE DATE not null,
RTURL VARCHAR2(800) not null,
EXPAND VARCHAR2(800) not null,
REMARK VARCHAR2(200),
FGSNO VARCHAR2(40),
FROMPROV CHAR(20),
FROMCITY CHAR(20),
FROMDISTRICT CHAR(30),
TOPROV CHAR(20),
TOCITY CHAR(20),
TOAREA CHAR(30),
AMOUNT CHAR(30),
VOLUME NUMBER(16,2),
WEIGHT NUMBER(16,2)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16K
minextents 1
maxextents unlimited
);
-- Create table
create table HY_WLDW
(
DWNO VARCHAR2(15) not null,
DWMC VARCHAR2(60) not null,
DWLX VARCHAR2(10),
SH VARCHAR2(20),
DZ VARCHAR2(80),
CS VARCHAR2(10),
SF VARCHAR2(10),
YZBM VARCHAR2(10),
LXR VARCHAR2(10),
DH VARCHAR2(30),
CZ VARCHAR2(15),
SRDA VARCHAR2(10),
DZYJ VARCHAR2(40),
BZ VARCHAR2(40),
SJNO VARCHAR2(15),
YWLX VARCHAR2(2),
WLYHNO VARCHAR2(15),
WLYHMC VARCHAR2(60),
XZJE NUMBER(15,4),
SXED NUMBER(12,2),
YSBZ VARCHAR2(1) default '0',
JSBZ VARCHAR2(1) default '1',
BZW VARCHAR2(2),
ZDJHBZ VARCHAR2(1) default '1',
YSFS VARCHAR2(20),
YSHSGS VARCHAR2(2),
YWLXGL VARCHAR2(30),
XTFL VARCHAR2(2),
JGRY VARCHAR2(15),
SRJE NUMBER(12,2),
KHLX VARCHAR2(2) default '1',
JSDW VARCHAR2(20),
CODE VARCHAR2(15),
ZPP VARCHAR2(1),
HYLB VARCHAR2(8) default 0,
DKBL NUMBER default 0,
KLRQ VARCHAR2(10),
ZYWMC VARCHAR2(60),
CKDZ VARCHAR2(60),
FZR VARCHAR2(30),
HWDZL NUMBER(12,4),
YZMC VARCHAR2(30),
YZQYRQ VARCHAR2(12),
WLYHFHNO VARCHAR2(20),
WLYHFHBM VARCHAR2(20),
WLYHFHCZY VARCHAR2(15),
WLYHFHDH VARCHAR2(25),
WLYHZHNO VARCHAR2(20),
WLYHZHBM VARCHAR2(20),
WLYHZHCZY VARCHAR2(15),
WLYHZHDH VARCHAR2(25),
JGFYSQRQ VARCHAR2(12),
JGFYSFPL NUMBER(8),
XMJSRQ VARCHAR2(12),
XMSX VARCHAR2(16),
XCRQ VARCHAR2(10),
DQDL VARCHAR2(36),
QY VARCHAR2(30),
HTJE NUMBER(12,4),
ZYWQD VARCHAR2(30),
ZYWSFTZD VARCHAR2(30),
ZYWQD_DJ VARCHAR2(20),
QSMX_DJ VARCHAR2(20),
SHTZS_DJ VARCHAR2(20),
SHTZSHZ_DJ VARCHAR2(20)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 704K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table HY_WLDW
add constraint DWNO primary key (DWNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
minextents 1
maxextents unlimited
);