CODEWITHSUNDEEP
mysqlcursorprocedure
Aditya Dwivedi
Aditya Dwivedi

Reputation: 64

what is wrong in this mysql cursor, and how to correct it?

CREATE PROCEDURE curLike()
    BEGIN
        DECLARE likeRec likecounttable;
        DECLARE c_likeCount CURSOR FOR SELECT l.likeCount, l.qId  FROM likecounttable l;

        OPEN c_likeCount;
        start_loop:LOOP
            FETCH c_likeCount IN likeRec
            UPDATE qentry SET qentry.likeCount = likeRec.likeCount WHERE qentry.qId=likeRec.qId;
        END LOOP;
        CLOSE c_likeCount;
    END;

I am trying to use a cursor here which fetches records from likecounttable, I saw this type of syntax in few sites so I used it but it is not working

Upvotes: 0

Views: 1204

Answers (2)

Napoli
Napoli

Reputation: 1403

You are missing a semi-colon after your first declaration, furthermore, likecounttable is a table, not a data type.

Since you're trying to store two column values into your declared variables, your first line should look more like this

DECLARE likeRec_Count, likeRec_qId INT;

After reading your code, if you aren't adding to your cursor, you can simplify by using the following sql instead, which does the same thing as your cursor.

UPDATE qentry JOIN likecounttable l ON l.qId=qentry.qId
  SET qentry.likeCount = l.likeCount
;

EDIT: If you wanted a complete update to your cursor, the following should do the same thing.

DELIMITER $$
CREATE PROCEDURE curLike()
    BEGIN
        DECLARE c_likeRec_isdone BOOLEAN DEFAULT FALSE;
        DECLARE likeRec_Count, likeRec_qId INT;
        DECLARE c_likeCount CURSOR FOR SELECT l.likeCount, l.qId  FROM likecounttable l;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_likeRec_isdone = TRUE;

        OPEN c_likeCount;
        loop_likeRecList: LOOP

           FETCH c_likeCount INTO likeRec_Count, likeRec_qId;
           IF c_likeRec_isdone THEN
              SET c_likeRec_isdone = FALSE;
              LEAVE loop_likeRecList;
           END IF;

           UPDATE qentry SET qentry.likeCount = likeRec_Count WHERE qentry.qId=likeRec_qId;

        END LOOP loop_likeRecList;
        CLOSE c_likeCount;
    END;
$$

Upvotes: 1

Kedar Limaye
Kedar Limaye

Reputation: 1041

CREATE PROCEDURE curLike()
    BEGIN
        DECLARE likeRec_Count, likeRec_qId INT;
        DECLARE c_likeCount CURSOR FOR SELECT l.likeCount, l.qId  FROM likecounttable l;

        OPEN c_likeCount;
        start_loop:LOOP
            FETCH c_likeCount INTO likeRec_Count,likeRec_qId 
            UPDATE qentry SET qentry.likeCount = likeRec_Count WHERE qentry.qId=likeRec_qId ;
        END LOOP;
        CLOSE c_likeCount;
    END;

Upvotes: 0

Related Questions