CODEWITHSUNDEEP
mysqlvariablesstored-proceduresmariadb
tmorais
tmorais

Reputation: 13

Mysql Procedures: SELECT and UPDATE

I'm trying to add this procedure. Its giving me error 1064 (42000) SQL Syntax. But I'm not sure where. 

DELIMITER //
 CREATE PROCEDURE disable_expired_users
 BEGIN
   DECLARE @disable_this INT;
   SET @disable_this = SELECT id FROM my_users WHERE TIMESTAMPDIFF(SECOND, NOW(), expire_date) <= 0;
   IF NOT @disable_this IS NULL THEN
     UPDATE my_users SET is_active = 0 WHERE id IN @disable_this;
   END IF;
END; //

Upvotes: 0

Views: 242

Answers (3)

Rick James
Rick James

Reputation: 142298

Why not simply

DELIMITER //
CREATE PROCEDURE disable_expired_users
BEGIN
    UPDATE my_users
        SET is_active = 0
        WHERE expire_date < NOW();
END; //

Even better -- don't store redundant data (is_active=0 vs expire_date<NOW()) -- simply use expire_date<NOW() whenever you need to check for expired.

If you are burdened by Workbench's restriction against UPDATE without blah blah, do something like this:

...
BEGIN
    SET @su = @@sql_safe_updates;
    SET sql_safe_updates = OFF;
    ... UPDATEs, etc ...
    SET sql_safe_updates = @su;
END //

Upvotes: 1

JohnZ
JohnZ

Reputation: 14

Add parentheses:

SET @disable_this = (SELECT id FROM my_users WHERE  
TIMESTAMPDIFF(SECOND, NOW(), expire_date) <= 0);

Upvotes: 0

Krishnakumar
Krishnakumar

Reputation: 745

SET @disable_this = SELECT id FROM my_users WHERE TIMESTAMPDIFF(SECOND,  
NOW(), expire_date) <= 0;

I think the problem is here.

Try 

SET @disable_this = (SELECT id FROM my_users WHERE  
TIMESTAMPDIFF(SECOND, NOW(), expire_date) <= 0);

Upvotes: 0

Related Questions