How can I retain inactive users for the stored procedure below-CodePudding

Im trying to retain the data from inactive users from the past 12 months And im getting a syntax error 1064:

near 'IFNULL(ub.updated_at, ub.inserted_at) < ( CONCAT(YEAR(NOW()), "_", MONTH(NOW())' at line 2 .

Here's the sample stored procedure

DELIMITER $$ 

CREATE PROCEDURE `logdb_user_breaks_delete`(

IN log_database VARCHAR(255) CHARACTER SET utf8mb4, 

IN retention_logdb INT(11) )

BEGIN SET @v = concat('DELETE FROM `',log_database ,'`.`user_breaks` ub WHERE

IFNULL(ub.updated_at, ub.inserted_at) < (CONCAT(YEAR(NOW()), "-", MONTH(NOW()), "-

", "01") - INTERVAL ',retention_logdb,' MONTH);');

PREPARE stm FROM @v;

EXECUTE stm; 

DEALLOCATE PREPARE stm;

END$$ DELIMITER ;

Im doing it in heidi sql version 10

Here's how I call the stored procedure :

CALL procedure_name(db_name,12);

Thanks for the help.

CodePudding user response：

Until MySQL 8.0, single-table DELETE statements cannot assign an alias to the table name. So remove the ub alias.

DELIMITER $$ 

CREATE PROCEDURE `logdb_user_breaks_delete`(IN log_database VARCHAR(255) CHARACTER SET utf8mb4, IN retention_logdb INT(11) )

BEGIN
    SET @v = concat('DELETE FROM `',log_database ,'`.`user_breaks` WHERE
IFNULL(updated_at, inserted_at) < (CONCAT(YEAR(NOW()), "-", MONTH(NOW()), "-", "01") - INTERVAL ', retention_logdb, ' MONTH);');

    PREPARE stm FROM @v;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;

END$$
DELIMITER ;