Update messages records to mark them as read

Question

Update messages records to mark them as read

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have this process stored, I need to make several updates to mark messages as read. The problem is that only 1 update is done and there are messages marked as unread.

CREATE DEFINER='root'@'localhost' PROCEDURE 'ShowMSG'(IN 'P_TO' INT, IN 'P_FROM' INT)
    BEGIN
        DECLARE v1 INT DEFAULT 0;
        DECLARE v2 INT DEFAULT 0;
        DECLARE v3 INT DEFAULT 0;
        SET v1 := 0; 
        SET v3  := (SELECT COUNT(MSG_id) FROM tbl_msg INNER JOIN tbl_empleados ON tbl_empleados.EM_id = tbl_msg.MSG_who WHERE MSG_who != P_FROM AND MSG_read = 0);
        WHILE v1 < v3 DO
            SET v2 := (SELECT MSG_id FROM tbl_msg INNER JOIN tbl_empleados ON tbl_empleados.EM_id = tbl_msg.MSG_who WHERE MSG_who != P_FROM AND MSG_read = 0 LIMIT v1,1);
            UPDATE tbl_msg SET MSG_read = 1 WHERE MSG_id = v2;
            SET v1 := v1 + 1;
        END WHILE;

        SELECT MSG_id, MSG_from, MSG_to, MSG_msg, MSG_who, MSG_send FROM tbl_msg WHERE (MSG_to = @P_TO || MSG_to = P_FROM) AND (MSG_from = P_TO || MSG_from = P_FROM);
    END

mysql

asked by J. Tinajero 08.09.2016 в 18:17

source

2 answers

1

If you update row by row, you could generate something like this:

UPDATE tabla SET campo = '1' WHERE id_tabla in (1,2,3,4,5,6,7,8,9);

where in (1,2,3,4,5,6,7,8,9) contains all the ids to update

answered by 08.09.2016 в 18:41

C ++ Problem with data entry With Django 'str' object is not callable in class based-views

score 2 · Accepted Answer

Any specific reason why you are doing the UPDATE row by row ?. Unless there is some external constraint, you should perform these operations as sets:

UPDATE tbl_msg m
INNER JOIN tbl_empleados e
    ON m.MSG_who = e.EM_id
    AND m. MSG_who != P_FROM 
    AND MSG_read = 0
SET MSG_read = 1