一、目标根据业务需要,进行选择性数据脚本升级插入动作。
二、关键属性Dual临时表,条件过滤,连接查询
三、代码-- 先在服务中添加优先级参数
INSERT INTO t_service_parameter
SELECT "2017052609325500014", (SELECT id FROM t_service_info WHERE service_code = "ServVSATDurn"),"QoSPrir","服务优先级参数",3,"high,medium,low"
FROM DUAL WHERE EXISTS(SELECT service_code FROM t_service_info WHERE service_code = 'ServVSATDurn');
INSERT INTO t_service_parameter
SELECT "2017052609325500015", (SELECT id FROM t_service_info WHERE service_code = "ServVSATTraf"),"QoSPrir","服务优先级参数",3,"high,medium,low"
FROM DUAL WHERE EXISTS(SELECT service_code FROM t_service_info WHERE service_code = 'ServVSATTraf');
-- 再在产品参数表中添加默认优先级参数
INSERT INTO t_product_parameter
SELECT r.id,r.id,p.id,"medium" FROM t_product_info r LEFT JOIN t_product_service s ON r.id = s.`product_id` LEFT JOIN t_service_parameter p ON s.service_id = p.service_id WHERE p.`parameter_code`='QoSPrir';
四、sql解释一、普通insert插入动作
insert into table(field1,field2,field3) values(val1,val2,val3)
二、加判断条件执行插入动作
insert into table(field1,field2,field3)
select val1,val2,val3 FROM DUAL WHERE EXISTS(sql condition);
a、如果不满足sql condition这个sql条件,将不会执行这个插入动作
b、中间表DUAL用来封装需要插入的数据,可以是批量的数据。