Get result occur from update statement inside stored procedures

Question

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?

Answer 1

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