批量修改A表中数据在B表中的映射数据

最近开发中遇到一个bug,报表查询条件多选时会有一个 dept in (0001,0002) 的条件,但是在数据库中dept设计的却不是数值类型,导致如果dept存在中文就会在查询时出现bug-> ORA-01722: 无效数字

为了解决此bug需要修改数据库中本应该是编码却因导入数据时没处理而插入了中文的数据,即根据部门-编码表的映射关系去修改数据库中的中文部门为数字编码。

先设计两个数据库模拟下实际出现的情况

1
2
3
4
5
6
7
-- 资产卡片表(数据出现问题的表)
CREATE TABLE `capcard` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`capname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`deptcode` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
-- 部门-编码映射表
CREATE TABLE `depts` (
`title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 插入数据
INSERT INTO `demo`.`depts` (`title`, `code`) VALUES ('综合部', '001');
INSERT INTO `demo`.`depts` (`title`, `code`) VALUES ('财务部', '002');
INSERT INTO `demo`.`depts` (`title`, `code`) VALUES ('研发部', '003');
INSERT INTO `demo`.`depts` (`title`, `code`) VALUES ('营销部', '004');

-- 这里的deptcode本应该插入类似001 002这样的编码却插入了中文

INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (1, '台式电脑1', '综合部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (2, '台式电脑2', '财务部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (3, '台式电脑2', '研发部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (4, '台式电脑1', '综合部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (5, '台式电脑2', '财务部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (6, '台式电脑3', '研发部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (7, '笔记本电脑1', '综合部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (8, '笔记本电脑2', '财务部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (9, '笔记本电脑3', '研发部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (10, '打印机1', '综合部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (11, '打印机2', '财务部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (12, '打印机3', '研发部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (13, '订书机1', '综合部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (14, '订书机2', '财务部');
INSERT INTO `demo`.`capcard` (`id`, `capname`, `deptcode`) VALUES (15, '订书机3', '研发部');

先查询一下capcard现在的数据

select * from capcard limit 10

1
2
3
4
5
6
7
8
9
10
11
12
id  capname    deptcode
1 台式电脑1 综合部
2 台式电脑2 财务部
3 台式电脑2 研发部
4 台式电脑1 综合部
5 台式电脑2 财务部
6 台式电脑3 研发部
7 笔记本电脑1 综合部
8 笔记本电脑2 财务部
9 笔记本电脑3 研发部
10 打印机1 综合部

执行更新语句

UPDATE capcard c LEFT JOIN depts d on c.deptcode=d.title set c.deptcode=d.CODE

再次查询capcard

select * from capcard limit 10

1
2
3
4
5
6
7
8
9
10
11
id  capname    deptcode
1 台式电脑1 001
2 台式电脑2 002
3 台式电脑2 003
4 台式电脑1 001
5 台式电脑2 002
6 台式电脑3 003
7 笔记本电脑1 001
8 笔记本电脑2 002
9 笔记本电脑3 003
10 打印机1 001

完美解决!!!

坚持技术分享,您的支持将鼓励我继续创作