0

I have been looking around, however i can not see my error,

My query

INSERT INTO p_location_check (location_id) VALUES (1) 
IF NOT EXISTS (SELECT approved, disapproved FROM p_location_check WHERE approved REGEXP '^1234568745$' OR disapproved = '^1234568745$' AND location_id=1);

after just for testing

INSERT INTO p_location_check (location_id) VALUES (1) 
IF NOT EXISTS (SELECT approved, disapproved FROM p_location_check WHERE approved = 1234568745 OR disapproved = 1234568745 AND location_id=1);

reponse

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 'IF NOT EXISTS (SELECT approved, disapproved FROM qp_location_check WHERE approve' at line 2

server version

Server version: 5.5.24-0ubuntu0.12.04.1

Barmar
  • 596,455
  • 48
  • 393
  • 495
Grmn
  • 500
  • 2
  • 9
  • possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) – Michael Berkowski Oct 02 '12 at 00:08
  • This is a job for `INSERT IGNORE` if location_id is a PK or unique key – Michael Berkowski Oct 02 '12 at 00:09
  • it is, but not in mysql location_id int(11) -- not unique `code`INSERT IGNORE INTO qp_location_check (location_id) VALUES (1) IF NOT EXISTS (SELECT `approved` AS a, `disapproved`AS b, `user_id` FROM qp_location_check WHERE `approved` REGEXP '/^9846513213$/'); – Grmn Oct 02 '12 at 00:10

1 Answers1

0

Edited:

Try this query -

INSERT INTO p_location_check (location_id) VALUES (1) FROM dual
WHERE (SELECT COUNT(*)
       FROM p_location_check
       WHERE approved = 1234568745 OR disapproved = 1234568745 AND location_id=1
       ) = 0;

Add your WHERE condition.

Devart
  • 110,991
  • 22
  • 156
  • 173
  • Ok that worked (query didnt come up with errors, however it is not inserting anything) it should thought. – Grmn Oct 02 '12 at 14:53
  • Also not sure what you doing with this part `code`ON t1.approved = t2.approved AND t1.disapproved = t2.disapproved AND t1.location_id = t2.location_id WHERE t2.approved IS NULL AND t2.disapproved IS NULL AND t2.location_id IS NULL`code` – Grmn Oct 02 '12 at 15:01
  • No errors, but not insert, where is the insert VALUE ? or where should it go! `code`INSERT INTO qp_location_check(location_id) SELECT DISTINCT(1) FROM qp_location_check t1 LEFT JOIN (SELECT * FROM qp_location_check WHERE approved REGEXP '^1234568745$' OR disapproved REGEXP '^1234568745$' AND location_id = 1) t2 ON t1.approved = t2.approved AND t1.disapproved = t2.disapproved AND t1.location_id = t2.location_id WHERE t2.approved REGEXP '^1234568745$' AND t2.disapproved REGEXP '^1234568745$' AND t2.location_id=1 – Grmn Oct 02 '12 at 15:06
  • I have changed solution completely. Try new one ;-) – Devart Oct 03 '12 at 13:00