CODEWITHSUNDEEP
mysqlstored-procedures
Shad
Shad

Reputation: 15451

MySQL Stored Procedure DELETEs all Rows instead of just one

I'm trying out stored procedures for the first time, and I can't figure out what I'm doing wrong.

Here's the table definition:

CREATE TABLE `answers` (
  `anid` int(11) unsigned NOT NULL auto_increment,
  `uid` int(11) NOT NULL,
  `dtid` int(11) NOT NULL,
  `answer` text NOT NULL,
  PRIMARY KEY  (`anid`),
  KEY `uid` (`uid`),
  KEY `dtid` (`dtid`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

anid is the primary key, uid is user id, dtid is datum id, and answer is the answer provided. Whenever I get a new answer for a given datum (question) and user id, I want to first delete any old answer to that same question by that same user, and then insert the new one.

Here's the procedure declaration:

DELIMITER //
CREATE PROCEDURE new_answer(uid INT(11),dtid INT(11),answer TEXT)
BEGIN
    DELETE FROM `answers` WHERE `uid` = uid AND `dtid` = dtid;
    INSERT INTO `answers` SET `uid` = uid, `dtid` = dtid, `answer` = answer;
END//

However, whenever I CALL new_answer ALL existing rows are deleted, and that one new answer is now the only row in the table. Hope it's something simple, thanks for your help.

Upvotes: 2

Views: 313

Answers (3)

ern0
ern0

Reputation: 3172

I'm not familiar with stored procedures, but what about renaming your function parameters to x and y instead of the very same as the column names?

Upvotes: 1

Mchl
Mchl

Reputation: 62387

You should probably try naming procedure arguments different than table columns.

Anyway, it looks like all you need is a single INSERT ... ON DUPLICATE KEY UPDATE query.

Upvotes: 2

yankee
yankee

Reputation: 40810

Rename your parameters:

DELIMITER //
CREATE PROCEDURE new_answer(p_uid INT(11),p_dtid INT(11),p_answer TEXT)
BEGIN
    DELETE FROM `answers` WHERE `uid` = p_uid AND `dtid` = p_dtid;
    INSERT INTO `answers` SET `uid` = p_uid, `dtid` = p_dtid, `answer` = p_answer;
END//

Upvotes: 4

Related Questions