3

I have this stored procedure:

DELIMITER $$
CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT)
 BEGIN
   DECLARE row_count INT(1) default 0;
   SELECT count(id) into row_count FROM trendContentMaster WHERE pid=numericid;
   IF row_count = 0 THEN
     INSERT INTO tMaster(tci,pid,tct,tcd,tcf,tcm,tcf,s)
       VALUES(mongoid,numericid,ctype,points,points,points,points,1);
   ELSE
     UPDATE tMaster 
       SET tcd = tcd + points,tcw = tcw + points,tcm = tcm + points,tcf = tcf + points 
       WHERE pid=numericid;
   END IF;
   INSERT INTO tTransaction(tci,pid,tct,act,acp,cd)
     VALUES(mongoid,numericid,ctype,activity,points,created);
 END $$
DELIMITER

Which works fine, except it updates tMaster field twice. For example existing value of tcd, tcw, tcm, tcf is 30 and points value is 20 rather than 50 it updates value to 70.

What am I doing wrong in the increment operation?

migrated from serverfault.com Jun 25 '15 at 17:39

This question came from our site for system and network administrators.

  • Is trendContentMaster.pid have an UNIQUE index? – Kondybas Jun 24 '15 at 14:16
  • no its not q unique index but yes there is an index on pid – user524694 Jun 25 '15 at 4:02
  • Use better grammar. You don't need a native level English, but a clear intention to use a correct grammar makes a much better impression. I fixed your question now, but next time I won't be here with you, and you will get much more downvotes or "low quality" flags. – peterh says reinstate Monica Jun 25 '15 at 15:56
1

If the table tMaster have the same pid inserted but is not in the table trendContentMaster it will update 20*(rows) depending how many times pid is inserted in tMaster table. This should work for you:

    DELIMITER $$
    CREATE PROCEDURE updateTrendContentStats(
    IN mongoid VARCHAR(255),
    IN numericid INT,
    IN points INT,
    IN ctype INT,
    IN activity INT,
    IN created INT)
     BEGIN
     DECLARE vCount INT(1) default 0;
     DECLARE vCount02 INT(1) default 0;

     SET vCount=(SELECT count(*) FROM trendContentMaster WHERE pid=numericid);
     SET vCount02=(SELECT count(*) FROM tMaster WHERE pid=numericid);

     IF (vCount = 0 && vCount02 = 0) THEN
        INSERT INTO tMaster(tci,pid,tct,tcd,tcf,tcm,tcf,s) VALUES(mongoid,numericid,ctype,points,points,points,points,1);
     ELSE
        UPDATE tMaster 
        SET tcd = tcd + points,
            tcw = tcw + points,
            tcm = tcm + points,
            tcf = tcf + points WHERE pid=numericid;
     END IF;
     INSERT INTO tTransaction(tci,pid,tct,act,acp,cd) VALUES(mongoid,numericid,ctype,activity,points,created);
     END $$
    DELIMITER

PD: You should make a Foreing key in tMaster.pid.

  • 1
    Hi Thanks For response , My stored procedure was working fine. There was a trigger configured on tTransaction table to do same updates. I removed trigger. – coder Jun 26 '15 at 10:24
  • It's nice to know you found the problem. – oNare Jul 1 '15 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.