0

So, i've been running a .js app that stores & updates a database on both of a game and a discord bot. I've been using so far this following sql query to add/update data, but i've been looking for a way to only update if ${user.id} does not exist in the databse.

 INSERT INTO usermine
        (userID, mineCOOLDOWN, coal_mine, silver_mine, gold_mine, diamond_mine)
        VALUES( ${user.id}, 1, 1, 1, 1, 1)
    ON DUPLICATE KEY UPDATE userID = ${user.id};

I've seen a few other examples in here, but i was not succeful to implement it properly in my project (errored out or did not worked at all)

So more exactly what i need to do is:

check if the ${user.id} is already in the database. If so don't insert anything.

If the ${user.id} is not in the database, it should insert it's {user.id} and have all the other variables set to 1.

Community
  • 1
  • 1
Zeta Reactor
  • 85
  • 1
  • 7
  • If you've already tried various things, please link to answers you've tried and mention the exact errors/results you got. –  Jul 07 '19 at 18:50

2 Answers2

1

You can use the INSERT IGNORE feature of MySQL as:

INSERT IGNORE INTO usermine
        (userID, mineCOOLDOWN, coal_mine, silver_mine, gold_mine, diamond_mine)
        VALUES( ${user.id}, 1, 1, 1, 1, 1)
Vivek
  • 783
  • 5
  • 11
  • Would this only insert if the values are not in the table right? As the ${user.id} would be unique and all the other data will change overtime from 1 to various values. – Zeta Reactor Jul 07 '19 at 20:48
  • @ZetaReactor In that case you should have a look into it: https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ – Vivek Jul 08 '19 at 04:29
0

So, i've been running a .js app that stores & updates a database on both of a game and a discord bot. I've been using so far this following sql query to add/update data, but i've been looking for a way to only update if ${user.id} does not exist in the databse.

You mean does not exist in table?

I think what you need is to create the table setting the id column as unique. Thus, trying to insert with the same id will simply fail.

e.g.

CREATE TABLE Persons ( userID int NOT NULL UNIQUE, mineCOOLDOWN int NOT NULL, coal_mine int, silver_mine int, gold_mine int, diamond_mine int );

larisoft
  • 191
  • 2
  • 5
  • More exactly if the specific value - ${user.id} that would be a number does not exist in the table. Each id is unique for each user. As much as i've noticed the current setup sets up everything back to 1 if it's called again ( for example an user has 33 coal_mine and 1 of everything else. On the next call of the query it updates everything back to 1) – Zeta Reactor Jul 07 '19 at 20:51