How to do an update in two tables that does not return "safe update mode" in MySQL?

Question

How to do an update in two tables that does not return "safe update mode" in MySQL?

Navigation

#1 by (1 votes)

3

I have two table, one of users, and one of validation codes.

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

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 error:

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

I researched google and advised using: SET SQL_SAFE_UPDATES = 0;

I then did this:

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 can not make the change.

Is there another way to perform this update in a single query without having to do a separate select from the update ??

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

sysusuarios TABLE

VerificationTABLE

mysql

asked by anonymous 18.06.2018 / 05:38

1 answer

Update part of web page with Electron. (Change only content and keep header, footer and fixed menu) How do I move a div vertically using pure JS?

score 1 · Accepted Answer

I do not know if it will suit you and if I understand correctly what you want to do, but an alternative that I really like to use is the functions of MySQL itself.

You can create one like this for you:

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 so:

SELECT VerificarUsuario(?);

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

There are certainly dozens of other methods to do this, but for me this is the method I most enjoy using.

SOLVING THE ERROR OF MARY DB

According to the Maria DB documentation , resolve this error if you get the error: 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 set of characters you choose, but it must be a distinct set of characters that does not cause further confusion.

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

See how the function can be inserted successfully 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 this solves your problem.