How to update two tables in an alternative way that does not return "safe update mode" in Mysql?

Question

I have two tables, one for users, and one for validation codes.

I do a code check in PHP and need to do a query in the validation table at the same time that I update this table disabling this code so that it is not validated again, and activate the user if the code is correct.

So I’m doing the update this way:

UPDATE `verificaconta` as `verif`  
INNER JOIN `sysusuarios` as `usuarios` ON `usuarios`.`cod` = `verif`.`cod`
  SET `verif`.`ativo` = '0', `usuarios`.`ativo` ='1' 
  WHERE `verif`.`codvalidacao` = ?'

And I was returning the following mistake:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and Reconnect.

I googled and advised to use: SET SQL_SAFE_UPDATES = 0;

So I did it this way:

SET SQL_SAFE_UPDATES = 0;
UPDATE `verificaconta` as `verif`  
INNER JOIN `sysusuarios` as `usuarios` ON `usuarios`.`cod` = `verif`.`cod`
  SET `verif`.`ativo` = '0', `usuarios`.`ativo` ='1' 
  WHERE `verif`.`codvalidacao` = ?'  
SET SQL_SAFE_UPDATES = 1;

On my local server, it worked fine, but when I try to run on the web server, it cannot perform the change.

There is another way to perform this update in a single query without having to do a separate update select??

---------- EDIT ----------------

TABLE sysusuarios

VERIFIED TABLE

Answer 1

I do not know if it will be good for you and if I understood right what you want to do, but an alternative that I like very much to use is the functions of Mysql itself.

You can create one for yourself:

CREATE FUNCTION `VerificarUsuario`(codverif char(128)) RETURNS tinyint(1)
BEGIN
DECLARE codusuario varchar(20);
SELECT `cod` INTO codusuario FROM `verificaconta` WHERE `codvalidacao` = codverif;
CASE WHEN codusuario IS NOT NULL THEN
       UPDATE `sysusuarios` SET `ativo`='1' WHERE `cod`= codusuario;
       UPDATE `verificaconta` SET `ativo`='0' WHERE `cod`= codusuario;
       RETURN TRUE;
       ELSE RETURN FALSE;
END CASE;
END

And then you call the function with a select like this:

SELECT VerificarUsuario(?);

I think it gets less complicated this way. So you send your code inside the function, and la in Mysql it checks if the code exists, and if it exists it updates the two tables and returns true (1) if it does not exist it returns false (0).

Surely there are dozens of other methods to do this, but for me this is the method I like to use the most.

SOLVING MARIA DB’S ERROR

According to the documentation of Maria DB, resolve as follows if the error occurs: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that Corresponds to your Mariadb server version for the right syntax to use near '' at line 3

The solution is to specify a distinct delimiter for the duration of the process, using the DELIMITER command. The delimiter can be any character set you choose, but it needs to be a distinct character set that no longer causes confusion.

// is a common choice and used across the knowledge base.

See how the function can be successfully inserted from the mysql client with the new delimiter.

DELIMITER //

CREATE FUNCTION `VerificarUsuario`(codverif char(128)) RETURNS tinyint(1)
BEGIN
DECLARE codusuario varchar(20);
SELECT `cod` INTO codusuario FROM `verificaconta` WHERE `codvalidacao` = codverif;
CASE WHEN codusuario IS NOT NULL THEN
       UPDATE `sysusuarios` SET `ativo`='1' WHERE `cod`= codusuario;
       UPDATE `verificaconta` SET `ativo`='0' WHERE `cod`= codusuario;
       RETURN TRUE;
       ELSE RETURN FALSE;
END CASE;
END

//

DELIMITER ;

I hope it will solve your problem.