informix一个update语句报错
create temp table shh
(
bankcode CHAR(12),
BusinessCode CHAR(15)
)
with no log;
load from jf1.unl delimiter '|'
insert into shh;
update prpjfcombus set bankcode=
(select a.bankcode from shh a
where a.BusinessCode=prpjfcombus.BusinessCode
);
运行上面语句报错：
391: Cannot insert a null into column (prpjfcombus.bankcode).
Error in line 4
Near character position 4
麻烦大家帮忙分析一下。
jf1.unl:
0801025500|898430159600500
0801025500|898430159604401
0801025500|898430159604501
0801025500|898430159604601
0801025500|898430159604701
0801025500|898430159604801
0801025500|898430159604901
0801025500|898430159605001
0801025500|898430159605101
0801025500|898430159605201
0801025500|898430159605301
0801025500|898430159605401
0801025500|898430163000234
0801025500|898430163000235
0801025500|898430163000236
0801025500|898430163000237
0801025500|898430163000238
0801025500|898430292231488
0801025500|898430163000239
0801025500|898430163000240
0801025500|898430163000241
0801025500|898430163000242
0801025500|898430163000243
0801025500|898430163000244
0801025500|898430163000267
0801025500|898430192230169
0801025500|898430359601506
0801025500|898430359601504
0801025500|898430359601500
0801025500|898430292231497
0801025500|898430292231491
0801025500|898430192230170
0801025500|898430192230171
(
bankcode CHAR(12),
BusinessCode CHAR(15)
)
with no log;
load from jf1.unl delimiter '|'
insert into shh;
update prpjfcombus set bankcode=
(select a.bankcode from shh a
where a.BusinessCode=prpjfcombus.BusinessCode
);
运行上面语句报错：
391: Cannot insert a null into column (prpjfcombus.bankcode).
Error in line 4
Near character position 4
麻烦大家帮忙分析一下。
jf1.unl:
0801025500|898430159600500
0801025500|898430159604401
0801025500|898430159604501
0801025500|898430159604601
0801025500|898430159604701
0801025500|898430159604801
0801025500|898430159604901
0801025500|898430159605001
0801025500|898430159605101
0801025500|898430159605201
0801025500|898430159605301
0801025500|898430159605401
0801025500|898430163000234
0801025500|898430163000235
0801025500|898430163000236
0801025500|898430163000237
0801025500|898430163000238
0801025500|898430292231488
0801025500|898430163000239
0801025500|898430163000240
0801025500|898430163000241
0801025500|898430163000242
0801025500|898430163000243
0801025500|898430163000244
0801025500|898430163000267
0801025500|898430192230169
0801025500|898430359601506
0801025500|898430359601504
0801025500|898430359601500
0801025500|898430292231497
0801025500|898430292231491
0801025500|898430192230170
0801025500|898430192230171
10 同行回答
表结构定义与实际数据不一致问题。
设计表结构式not null不是适合所有表的字段，需要根据实际情况进行定义。
如按业务需求定义为not null， 那么楼主的问题就应该解决数据的问题，楼上各位提出了多个解决方式：
新版本推荐merge，性能更佳 收起
设计表结构式not null不是适合所有表的字段，需要根据实际情况进行定义。
如按业务需求定义为not null， 那么楼主的问题就应该解决数据的问题，楼上各位提出了多个解决方式：
新版本推荐merge，性能更佳 收起
update prpjfcombus set bankcode=
(select a.bankcode from shh a
where a.BusinessCode=prpjfcombus.BusinessCode
and( a.bankcode is not null or a.bankcode <>'')
)
where
BusinessCode
in(
select BusinessCode from shh) 收起
(select a.bankcode from shh a
where a.BusinessCode=prpjfcombus.BusinessCode
and( a.bankcode is not null or a.bankcode <>'')
)
where
BusinessCode
in(
select BusinessCode from shh) 收起
update prpjfcombus set bankcode=
(select a.bankcode from shh a
where a.BusinessCode=prpjfcombus.BusinessCode
);
很明显是你的sql写的有问题，连where 条件都没有，这样会全部更新，找不到对应记录的肯定update成null了 收起
(select a.bankcode from shh a
where a.BusinessCode=prpjfcombus.BusinessCode
);
很明显是你的sql写的有问题，连where 条件都没有，这样会全部更新，找不到对应记录的肯定update成null了 收起