1

I want to write a MySQL proc which takes userId and userName and does an insert or update into table users dependent on if a row exists where users.userId = userId

I saw a similar question here: MySql create or update row with ip?

But I want to do this in a stored procedure and wondered if that gives any additional tools?

Community
  • 1
  • 1
Mr. Boy
  • 52,885
  • 84
  • 282
  • 517

1 Answers1

2

You don't need a stored procedure, you can use INSERT ... ON DUPLICATE KEY UPDATE .... Assuming that userid is the PRIMARY or a UNIQUE key, this will work:

INSERT INTO user
SET 
userid = ?,
userName = ?
ON DUPLICATE KEY UPDATE
userName = VALUES(userName)

Of course, you could wrap this query in a stored procedure if you want:

DROP PROCEDURE IF EXISTS set_user;

DELIMITER //

CREATE PROCEDURE set_user(
    IN i_userid INT UNSIGNED,
    IN v_userName VARCHAR(50)
)
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
    INSERT INTO user
    SET 
    userid = i_userid,
    userName = v_userName
    ON DUPLICATE KEY UPDATE
    userName = VALUES(userName);
END;
//

DELIMITER ;
Ross Smith II
  • 10,928
  • 1
  • 32
  • 41
  • I prefer it to be a stored proc though... would I simply wrap that in a procedure body to get what I want? – Mr. Boy Nov 22 '12 at 21:54
  • Simply to decouple DB structure from code... we might want to do other things later, the DB might change, etc. – Mr. Boy Nov 22 '12 at 22:35