insert语句带条件
IF()THEN();
IF NOT EXISTS();
以上两种写法都会报错,下面的写法是对的。
模板
INSERT INTO table(field1, field2, fieldn) SELECT 'field1', 'field2', 'fieldn' FROM DUAL WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)
注:DUAL 是一个临时表不用创建
示例
INSERT INTO tb_cloud_laborun (`id`, `laborun_code`, `laborun_name`, `laborun_addr`, `laborun_credit_code`, `laborun_kind`, `laborun_type`, `laborun_kind_name`, `laborun_type_name`, `laborun_status`, `laborun_level`, `establish_time`, `build_way`, `parent_laborun_name`, `parent_laborun_code`, `administrat_name`, `administrat_code`, `legal_person_flag`, `laborun_legal_person`, `principal`, `principal_phone`, `congress_date`, `congress_expire`, `review_flag`, `women_flag`, `congress_regime`, `work_open`, `race`, `bookroom`, `bookroom_date`, `books`, `site_flag`, `sub_laborun_nums`, `laborun_team_nums`, `approval_doc_no`, `approval_doc_date`, `approval_unit`, `level`, `is_leaf`, `sort`, `is_deleted`, `remark`, `create_time`, `update_time`, `level_code`, `current_level_code`, `is_tax`, `unit_type`, `workers`, `peasant_workers`, `is_consummate`, `unit_name`, `is_laborun`) SELECT '1309068612606226432', '140305030012', '矿区西城影院工会委员会', NULL, NULL, NULL, '1', NULL, NULL, '1', '3', '2020-09-24 17:54:13', NULL, '矿区总工会', '140305020000', NULL, '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '3', '0', NULL, '0', NULL, '2020-09-24 17:54:13', NULL, '3', '001003012', '0', NULL, NULL, NULL, '1', '矿区西城影院', '1' FROM DUAL WHERE NOT EXISTS(SELECT t.* FROM tb_cloud_laborun WHERE id = '1309068612606226432' or laborun_code='140305030012');