merge 多条数据一起处理的问题，急急急急急急急！~~~

[dnmaijwwda]

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')
求找错！~~~~~~~

[dnmaijwwda]

求高手解决！~~

[cosio]

提示什么错误嘛?

[xiaobn_cn]

建议楼主把两个表的建表语句也发上来，只是看SQL没有发现问题。

[malong123456767]

首先看一下你的merge on 后面的条件是不是用的 a 表的主键做的判断，另外注意一下你b表里面的SELLERID值是否含有重复

[dnmaijwwda]

-- Create table
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
);

[dnmaijwwda]

唉，看着都头晕啊！~~

[dnmaijwwda]

如果是in里边是一个就可以，多个不可以。。

[xiaobn_cn]

按照楼主提供的建表与merge语句，我在oracle库上进行了测试，merge语句可以正常执行，没有语法上的错误。如果是语句运行的结果不符合期望目标的情况，请楼主详细说明表中的相关数据及期望目标。

[dnmaijwwda]

--!貌似是数据库卡了，今天早上重新打开就可以了。。。