How not to update empty fields

Asked

Viewed 119 times

1

Would like to nay update the values sent as empty, as the second argument passed from example below, but I want to avoid using an IF, I do not know if mysql provides some function that can help me

Call from the example proc:

call AtualizarCadastro('Valor1', '', 'DoutorPinpolho', 4)

Proc created below:

CREATE PROCEDURE `AtualizarCadastro`(in nome varchar(100), in sobrenome varchar(100), in senha varchar(100), in id int)
    BEGIN 

    UPDATE UsuariosLogin SET 

     `Nome`   nome
     `Sobrenome`   = sobrenome
     `Senha`   = senha

    where IdUsuario = id;

    END
mysql sql-server

  • generally this validation is not done in the bank but in its back-end, before the call of the past

  • Yes, but I do not want to do this validation, and as the database has its own language I believe it can do this natively, since it is a more specific data processing for the database

2 answers

1

COALESCE(valor, ...)

Returns the first non-null value of the list of values. If there is no value other than NULL then returns NULL.

Could be, for example:

COALESCE(valor, 'Não informado')
  • 1

    But let’s assume that I have a field with password and this field comes empty of a form, only if the guy does not enter the password it will remain blank, but in the bank the password is already existing, but as it is update form the password does not appear but if he wants to change he has to fill, IE, the function will be without the password and when it is so I want the field to remain what is already in the bank, gave to understand?

  • In this case enter 3 values: the given password, the existing password (which you recovered from the table) and finally the information of not informed.

  • But it’s just what I don’t want to do, I just want to accomplish all this flow just, but only by bank, so I didn’t involve anything in the backend in the question

  • How not to involve backend????

  • You ask to involve the back, because you may only know backend, but who knows SQL will know in sql, and simply do not want to involve, I want to solve this situation, if it were otherwise with backend I also would not have the need to ask in stackoverflow

  • Ma where you intend to do an UPDATE of your database otherwise in backend?

  • It already recovers the value of the model, but only do not recover the password

Show 2 more comments

1

Update tabela
set coluna = coalesce(valorParametro, coluna)
Where IdUsuario = id;

  • He wasn’t understanding, "valorColuna"???

  • Generic name for table column. I will rename to be more understandable

  • I tested it now and it didn’t work

  • CREATE DEFINER=root@% PROCEDURE USP_AtualizarCadastroPaciente(in first name varchar(100), in last name varchar(100), in password varchar(100), in id int) BEGIN UPDATE Usuarioslogin SET Nome = surname, Sobrenome = surname, Senha = Coalesce (password, Senha) Where Idusuario = id; END

Browser other questions tagged mysql sql-server

You are not signed in. Login or sign up in order to post.