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
  • 312
  • 4
  • 17
  • 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
  • Did one of these answers solve your problem? If not, could you provide more information to help answer it? Otherwise, please consider marking the answer which best solved your problem accepted (the check mark under the up/down vote arrows). See https://stackoverflow.com/help/someone-answers – Nick Jan 16 '20 at 12:53

3 Answers3

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,661
  • 12
  • 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
  • 362,140
  • 107
  • 528
  • 764
0

You can use an INSERT ... SELECT query to achieve this functionality:

INSERT INTO emp_master (token_no, vehicle_no) 
SELECT 1234, 12345
WHERE NOT EXISTS (SELECT * FROM emp_master WHERE vehicle_no = '12345')

Demo on dbfiddle

Nick
  • 118,076
  • 20
  • 42
  • 73