CODEWITHSUNDEEP
javasqlsqlite
Keith M
Keith M

Reputation: 1239

INSERT OR IGNORE inserting duplicate statements

So I'm having issues with my SQL queries, first I run this to make sure that the entry is in the database:

 "INSERT OR IGNORE INTO `scores` (`uuid`) VALUES ('" + stat.player + "')"

Next I run:

"UPDATE scores SET uuid = '" + stat.player + "', level = '"
                + stat.level + "', xp = '" + stat.xp
                + "', xpToNextLevel = '" + stat.xpToNextLevel
                + "', attackPoints = '" + stat.attackPoints
                + "', defencePoints = '" + stat.defencePoints
                + "', kills = '" + stat.kills + "', deaths = '"
                + stat.deaths + "', monsterKills = '" + stat.monsterKills
                + "' WHERE uuid = '" + stat.player + "'"\

My problem is that running the "INSERT OR IGNORE" isn't ignoring if there is a duplicate value. It will insert a new row with the same uuid every time I run it, and update all the rows with the same uuid.

I'm guessing I'm probably missing something, if you could help out I would really appreciate it.

My CREATE TABLE statement is:

CREATE TABLE scores (
    uuid varchar(128) NOT NULL,
    level int(11) NOT NULL DEFAULT '1',
    xp int(11) NOT NULL DEFAULT '0',
    xpToNextLevel int(11) NOT NULL DEFAULT '500',
    attackPoints int(11) NOT NULL DEFAULT '0',
    defencePoints int(11) NOT NULL DEFAULT '0',
    kills int(11) NOT NULL DEFAULT '0',
    deaths int(11) NOT NULL DEFAULT '0',
    monsterKills int(11) NOT NULL DEFAULT '0')

Upvotes: 1

Views: 112

Answers (1)

durron597
durron597

Reputation: 32323

Your INSERT OR IGNORE SQL statement looks good. Most likely your uuid is not set to be UNIQUE or PRIMARY KEY

Try this CREATE TABLE instead:

CREATE TABLE scores (
    uuid varchar(128) PRIMARY KEY,
    level int(11) NOT NULL DEFAULT '1',
    xp int(11) NOT NULL DEFAULT '0',
    xpToNextLevel int(11) NOT NULL DEFAULT '500',
    attackPoints int(11) NOT NULL DEFAULT '0',
    defencePoints int(11) NOT NULL DEFAULT '0',
    kills int(11) NOT NULL DEFAULT '0',
    deaths int(11) NOT NULL DEFAULT '0',
    monsterKills int(11) NOT NULL DEFAULT '0')

Upvotes: 2

Related Questions