mysql - 在将数据添加到mysql数据库的表之前检查

Question

我正在尝试在表 emp_master 中插入数据，其中 token_no 和vehicle_no 可用，我正在使用下面提到的查询来添加和检查记录

insert into emp_master (token_no, vehicle_no) values (1234,12345) where not exists (select * from emp_master where vehicle_no = '12345');

但是每当我尝试这个时，就会出现错误

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where not exists (select * from emp_master where vehicle_no = '12345')' at line 1

这个建议会有帮助

Answer 1

如果您需要在插入语句中进行此检查，您可以这样构造它:

SQL Fiddle:http://sqlfiddle.com/#!9/d3dd77/1

insert into emp_master (token_no, vehicle_no) 
select 1234, 12345
from dual
where not exists (select * from emp_master where vehicle_no = '12345');

在任何 DBMS 上执行此操作的另一种方法:

<强> DB Fiddle Demo

insert into emp_master (token_no, vehicle_no) 
select token_no, vehicle_no
from (
select 1234 token_no, 12345 vehicle_no
) rec
where not exists (select * from emp_master where vehicle_no = rec.vehicle_no);