CODEWITHSUNDEEP
mysqlsqlif-statementstored-procedures
Major Squirrel
Major Squirrel

Reputation: 61

"INSERT IF NOT EXISTS", ELSE UPDATE certain columns in MySQL

I'm using MySQL Workbench (6.3) and I'm trying to create a stored procedure with a specific "INSERT IF NOT EXSISTS" but I think I don't do it well. By the way, I did read this topic http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html and tried both examples, it didn't work. Here is the statement :

CREATE DEFINER=`test`@`localhost` PROCEDURE `onPlayerJoin`(
    IN uuid CHAR(36),
    IN nickname CHAR(16),
    IN firstConnection TIMESTAMP,
    IN lastConnection TIMESTAMP
)

BEGIN
    INSERT IGNORE INTO `test`.`player` (`playerUuid`, `playerNickname`, `playerFirstConnection`, `playerLastConnection`)
        VALUES (uuid, nickname, firstConnection, lastConnection);
        UPDATE `test`.`player` SET
        `playerNickname` = nickname,
        `playerLastConnection` = lastConnection
        WHERE `playerUuid` = uuid;
END

IF the PK isn't found, INSERT it. ELSE, UPDATE certain columns. (that I can specified) However, it seems that it updates every column, which I would like to restrict to certain columns. Here is my procedure : http://pastebin.com/NfcdU9Rb !

Optional question : is it injection-safe ?

Upvotes: 0

Views: 854

Answers (1)

seahawk
seahawk

Reputation: 1912

Use INSERT ... ON DUPLICATE KEY UPDATE Syntax

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Kindly refer the link for details: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

Upvotes: 1

Related Questions