DROP TABLE IF EXISTS tab1;
DROP TABLE IF EXISTS tab2;
DROP PROCEDURE IF EXISTS sp_test;

CREATE TABLE tab1 (t1_col1 int primary key);
CREATE TABLE tab2 (t2_col1 int primary key);

INSERT tab1 VALUES (1), (2), (3), (4);
INSERT tab2 VALUES (1), (2), (3), (4);

CREATE PROCEDURE sp_test()
BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
GET DIAGNOSTICS CONDITION 1 @HANDLER_TEXT = MESSAGE_TEXT;
SELECT @HANDLER_TEXT;
END;

UPDATE tab1 SET t1_col1 = 1 WHERE t1_col1 = 4;
UPDATE tab1 INNER JOIN tab2 ON t2_col1 = t1_col1 SET t1_col1 = 2 WHERE t1_col1 = 4;
UPDATE tab1 LEFT JOIN tab2 ON t2_col1 = t1_col1 SET t1_col1 = 3 WHERE t1_col1 = 4;

– The UPDATE using LEFT JOIN is not captured by the continue handler. Why?

END;

CALL sp_test();

Output:

+---------------------------------------+

| @HANDLER_TEXT                         |

+---------------------------------------+

| Duplicate entry '1' for key 'PRIMARY' |

+---------------------------------------+

+---------------------------------------+

| @HANDLER_TEXT                         |

+---------------------------------------+

| Duplicate entry '2' for key 'PRIMARY' |

+---------------------------------------+

ERROR 1062 (23000) at line 1: Duplicate entry '3' for key 'PRIMARY'