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

    skipperguy12

    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

    Ne0nx3r0

  3. Offline

    skipperguy12

    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

    Ne0nx3r0

    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

    skipperguy12

    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 resultset.next() is not null. if it is, return false, meaning the player does not exist.
     
  6. Offline

    toothplck1

    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

    skipperguy12

    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

    toothplck1

    You could delete and remake the table if you really needed to or you could I think use this:
    ALTER TABLE tablename AUTO_INCREMENT = 1
     
  9. Offline

    skipperguy12

    Thanks man!

    Edit:
    I remember trying to use your methods for sql...it 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