CODEWITHSUNDEEP
mysqlsql-update
Jacob Macallan
Jacob Macallan

Reputation: 979

Update if exists, otherwise insert (without unique keys)

I have a table that is created like this: 

 'CREATE TABLE `boss_kills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `characterid` int(11) DEFAULT NULL,
  `mobid` int(11) DEFAULT NULL,
  `amount` int(11) NOT NULL DEFAULT ''0'',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1'

Goal: I'm trying to create a system where a player kills a boss in the game and it records the boss's ID and the player's ID in the table. I want to be able to write one query where it updates if the player is already logged with the specific boss and inserts if he or she isn't.

Research: I did a lot of research online and people suggest the INSERT INTO ... ON DUPLICATE KEY UPDATE, but that only works if you either know your primary key or have a unique key, none of which I have or know.

Tries: I've tried 

IF EXISTS (...) THEN UPDATE (...) ELSE INSERT(...)

and 

UPDATE (...) IF @@ROWCOUNT = 0 INSERT INTO (...)

but they don't work. I get syntax errors.

If needed I can provide the errors thrown by the above tries. My current code for trying to update is this (but it throws a SQL syntax error):

Attempt 1:

UPDATE boss_kills 
  SET amount = amount + 1 
  WHERE characterid = ? AND mobid = ? 
IF @@ROWCOUNT = 0 
  INSERT INTO boss_kills (characterid, mobid, amount) VALUES (?, ?, 1)

Attempt 2:

IF NOT EXISTS (SELECT id FROM boss_kills WHERE characterid = ? AND mobid = ?) 
THEN 
  INSERT INTO boss_kills VALUES (DEFAULT, ?, ?, 1) 
ELSE 
  UPDATE boss_kills SET amount = amount + 1 WHERE characterid = ? AND mobid = ?

Upvotes: 1

Views: 2394

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

It seems like (characterid, mobid) could make a unique index constraint for your table, thus allowing you to use INSERT ... ON DUPLICATE KEY UPDATE.

Use below script to create your table

CREATE TABLE boss_kills (
  id int(11) NOT NULL AUTO_INCREMENT,
  characterid int(11) DEFAULT NULL,
  mobid int(11) DEFAULT NULL,
  amount int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY ( id ),
  UNIQUE ( characterid, mobid )
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;

Note that I've removed backticks around your table and column names - they are not necessary.

Inserting a row

INSERT INTO boss_kills (characterid, mobid, amount)
  VALUES (?, ?, 1)
  ON DUPLICATE KEY UPDATE amount = amount + 1;

Upvotes: 3

Related Questions