CODEWITHSUNDEEP
mysqlsql
DomeWTF
DomeWTF

Reputation: 2420

MySQL syntax error : where not exists

I've been trying to insert a record in the database if it does not already exists and this is the query:

insert into evt(e_id, t_id)
values( '1597',  '4')
where not exists( select id from evt where e_id = '4' and t_id = '1597');

that query returns with the following error:

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 id from evt where e_id = '4' and t_id = ' at line 1

I can't understand why it doesn't work

Upvotes: 2

Views: 3000

Answers (7)

kuldeep upadhyay
kuldeep upadhyay

Reputation: 42

I think you are looking for UPDATE and not insert?

UPDATE `evt` SET `e_id`='1597', `t_id`='4' 
       WHERE `e_id` NOT in ('4') and `t_id` not in('1597');

Upvotes: 0

Kaval Patel
Kaval Patel

Reputation: 668

Try this :

    INSERT INTO evt(e_id, t_id)
SELECT * FROM (SELECT '4', '1597') AS tmp
WHERE NOT EXISTS (
    SELECT e_id FROM evt WHERE e_id = '4' and t_id = '1597'
) LIMIT 1;

Upvotes: 2

DomeWTF
DomeWTF

Reputation: 2420

I solved everything adding a unique pair like this:

ALTER IGNORE TABLE mytable ADD UNIQUE (field1,field2)

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

MySQL INSERT Syntax does not support the WHERE condition 

 insert into evt(e_id, t_id) values( '1597',  '4')

could be you need update 

  update evt
  set e_id = '1597',
  t_id  = 4
  where id  not in ( select id from evt where e_id = '4' and t_id = '1597');

Or an insert select 

 insert into evt(e_id, t_id) 
 select  '1597',  '4'
 from evt
 where not exists( select id from evt where e_id = '4' and t_id = '1597');

Upvotes: -1

user3677687
user3677687

Reputation:

INSERT statement doesn't support WHERE clause.

You have multiple options. First do a select and if the condition is true INSERT OR Use ON DUPLICATE KEY

INSERT INTO evt
    SET e_id = '1597', t_id = '4'
ON DUPLICATE KEY UPDATE
    e_id = '1597', t_id = '4'

This needs your field to be an index ( Primary or unique )

Note: you shouldn't use quotes when inserting numbers as the mysql treat it like a string and tries to do a conversion and gradually it slows down your project. Also it is safer to insert numbers without quotes

Upvotes: 0

Angelov
Angelov

Reputation: 51

Try this:

 insert into evt(e_id, t_id) values( '1597',  '4') where not in( select id from evt where e_id = '4' and t_id = '1597');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

If you want to prevent duplicates in the database, then let the database do the work. Use a unique constraint or index:

create unique index unq_evt_eid_tid on evt(e_id, t_id);

This guarantees the data integrity, regardless of how values are inserted or updated. The database does the work for you.

Then you can prevent an error when inserting, by using on duplicate key update:

insert into evt(e_id, t_id)
     values (1597,  4) 
     on duplicate key update e_id = values(e_id);

The on duplicate key prevents the code from generating an error.

I also removed the single quotes from the values. Ids are usually numbers. If they are really strings, add the quotes back in.

Upvotes: 7

Related Questions