CODEWITHSUNDEEP
mysqlstored-proceduresrows-affected
palAlaa
palAlaa

Reputation: 9858

Get result occur from update statement inside stored procedures

I want to check the existance of specific record in db table, if it's exist then update if not I want to add new record

I am using stored procedures to do so, First I make update stetement and want to check if it occurs and return 0 then there's no record affected by update statement and that means the record does not exist.

I make like this 

DELIMITER //
CREATE PROCEDURE revokePrivilegeFromUsers(IN userId int(11),  IN privilegeId int(11), IN deletedBy int(11))

BEGIN
  DECLARE isExist int;

  isExist = update `user_privileges` set `mode` ='d' ,`updated_by` = deletedBy, `date_time_assigned` =  CURRENT_TIMESTAMP()  where `user_id`= userId and `privilege_id`=privilegeId;

 IF  isExist  == 0 THEN
      insert into `user_privileges`(`user_id`,`privilege_id`,`mode`,`date_time_assigned`,`updated_by`)values (userId ,privilegeId ,'d',CURRENT_TIMESTAMP(),deletedBy );

 END IF;


END //

DELIMITER ;

This error occur with me

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= update `user_privileges` set `mode` ='d' ,`updated_by` = deletedBy, `date_time' at line 6

Is the way I am working is supported by mysql?

Upvotes: 2

Views: 4476

Answers (2)

palAlaa
palAlaa

Reputation: 9858

I solve the problem, I had 2 prblems

  1. ROW_COUNT() is used to get the number of rows affected in insert, update or delete statements.
  2. Equals comparison in stored procedure is = not ==

The correct stored procedure is 

DELIMITER //
CREATE PROCEDURE revokePrivilegeFromUsers(IN userId int(11),  IN privilegeId int(11), IN deletedBy int(11))

BEGIN
DECLARE count int default -1;

update `user_privileges` set `mode` ='d' ,`updated_by` = deletedBy, `date_time_assigned` =  CURRENT_TIMESTAMP()  where `user_id`= userId and `privilege_id`=privilegeId;
SELECT ROW_COUNT() into count ;
IF count  = 0 THEN
       insert into `user_privileges`(`user_id`,`privilege_id`,`mode`,`date_time_assigned`,`updated_by`)values (userId ,privilegeId ,'d',CURRENT_TIMESTAMP(),deletedBy );

END IF;
END //

DELIMITER ;

Upvotes: 1

Alex Monthy
Alex Monthy

Reputation: 1877

Use the INSERT IGNORE statement instead. I assume that your table has (user_id, privilege_id) as a unique key. 

insert ignore into user_privileges (user_id,privilege_id,`mode,date_time_assigned,updated_by)
values (userId ,privilegeId ,'d',CURRENT_TIMESTAMP(),deletedBy )
on duplicate key update mode='d', date_time_assigned=now(),updated_by=deletedBy

Upvotes: 0

Related Questions