0

I'm having a little trouble with my SQL queries and I keep getting a syntax error I can't figure out.

The query in question is:

INSERT INTO $db (uuid, quest, stage, completion, quest_id, dstart) VALUES ('".$avatar_key."','".$quest_name."','".$quest_stage."','".$qc."','".$quest_id."','".$date."') WHERE NOT EXISTS (SELECT *
       FROM $db
       WHERE uuid = '$avatar_key' AND quest_id = '$quest_id'
       )

What I'm trying to do is to check if the table has a row, with both the exact values of $avatar_key and $quest_id, and if such a row does NOT exist, then I want it to INSERT a new one.

I'm getting an error I can't seem to figure out how to fix though; the query itself looks to me (though I'm a SQL newbie) that is /should/ work. I'm using SQL 5.5, & this is the error message I'm getting:

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 sldb_quest WHERE uuid = '0089dcea-' at line 1

I know that having a unique identifier would've helped that, but the database is going to store a LOT of rows, many of which will have identical values and the only unique value will be the UUID, which I must then match up with the quest_id in order to find the specific row I want. The uuids look something like this: "0089dcea-bf39-40f5-859e-d79bdc383f1b" and are uniquely generated for every user.

Any pointers as to what I'm doing wrong would be very appreciated! I'm sure it's just a small detail I've managed to miss somewhere, but I've not had any luck finding the cause.

Martin
  • 40
  • 5
  • Possible duplicate of http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql – PinnyM Dec 06 '12 at 17:56

1 Answers1

1

A WHERE clause is not valid on a INSERT with a VALUES list. You can use INSERT ... ON DUPLICATE UPDATE, but I'm not sure an update is what you want.

If uuid (with or without quest_id) is set as a unique key, the simplest thing you can try is to use IGNORE.

INSERT IGNORE INTO $db (uuid, quest, stage, completion, quest_id, dstart) 
VALUES ('".$avatar_key."','".$quest_name."','".$quest_stage."','".$qc."','".$quest_id."','".$date."')

And if you don't have a unique key yet, I strongly recommend you make one.

Community
  • 1
  • 1
PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • I guess I could give it a try with the unique key thing, though I'm not sure how that's going to affect different entries with the same uuid but different quest_id? The 3rd party script that needs this information isn't able to do the query natively (due to being in a different environment incompatible with SQL), so there's no way it can know the unique keys of the rows in the DB it needs to query. The way I've to do it is to send a URL request to the PHP file, which then queries & prints the values on page. Then I typecast the content of the page into a list inside the 3rd party script. – Martin Dec 06 '12 at 18:51
  • As long as you include both columns (uuid and quest_id) in the unique key, you should be fine. – PinnyM Dec 06 '12 at 19:00