0

I am trying to insert data in table emp_master where token_no and vehicle_no is available, I am using mentioned below query to add and check the record 

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

but whenever I am trying this then error comes in as

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

The suggestion will be helpful

varul jain
  • 334
  • 8
  • 23
  • You cannot have WHERE in INSERT STATEMENT. You need to create before insert trigger and check everything there. Or use FK to prevent inserting record that is related to non-existing identifiers. – fifonik Jan 14 '20 at 03:45
  • This link might help https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table – Ved Jan 14 '20 at 03:46

2 Answers2

1

If you need to have this check in your insert statement, you can structure it like this:

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');

Another way to do this on any 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);
rohitvats
  • 1,811
  • 13
  • 11
0

Put a UNIQUE constraint on the vehicle_no column, and then insert the data with no WHERE clause. Instead, handle the exception if it fails.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794