Solved [SQL] - How do I INSERT INTO if not exists?

Discussion in 'Plugin Development' started by skipperguy12, Jan 11, 2013.

Thread Status:
Not open for further replies.
  1. Offline


    For my plugin, i'm making something where a player is added to the table if hes not already there. Why does this statement not work:
    IF NOT EXISTS (SELECT * FROM `playerdata` WHERE player='bob') BEGIN (INSERT INTO playerdata(player, totalkills) VALUES ('bob', 1)


    Error (sql error):
    #1064 - 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 * FROM playerdata WHERE player='bob') BEGIN (INSERT INTO p' at line 1
  2. Offline


  3. Offline


    INSERT IGNORE `playerdata`(`player`, `totalkills`) VALUES ('bob', 10)

    makes multiple bob's (._.)

    How would I use ON DUPLICATE?

    Ne0nx3r0 Lets just say I don't want duplicates. If the player exists, just return, exit the task.

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
    Last edited by a moderator: Jul 8, 2016
  4. Offline


    It sounds like your primary key(s) are not setup correctly; make sure the table only allows one entry for each player by making 'player' either the/part of the primary key, or by assigning a unique index to it.

    Then you can use on duplicate to add one to the value, or whatever else you need to do.

    You can also use insert ignore to ensure only one entry exists.
  5. Offline


    Solved, you don't need to use IGNORE and what not. You can just make a method to check if the player exists by doing this statement:
    SELECT * FROM playerdata WHERE player = '" + player +"'"

    and check if is not null. if it is, return false, meaning the player does not exist.
  6. Offline


    You could use UPDATE which will create the row if it doesnt already exist. And if it does exist it updates the values.
  7. Offline


    Ah well, I got it to work! How do I reset the ID? What I mean is, because I made a lot of tests and test users, and I deleted them...everytime I make a new user he starts with a number way off...I know its not a big deal, but its annoying to see the first user have ID 22 ;n;
  8. Offline


    You could delete and remake the table if you really needed to or you could I think use this:
  9. Offline


    Thanks man!

    I remember trying to use your methods for took me a DAMN long time to figure out that it wouldn't work for me, lol!
Thread Status:
Not open for further replies.

Share This Page