SQL if not null update

GMB 2020-01-31 18:01

If email OR password is not null update them otherwise let them be as they are.

You can use case expressions for this. I think that the logic you want is:

UPDATE users 
SET 
    username = Param1
    email = case when email is not null then Param2 end,
    password = case when password is not null then Param3 end
WHERE id = Param4;

Or if you want to update email and password if both are not null then:

UPDATE users 
SET 
    username = Param1
    email = case when email is not null and password is not null then Param2 end,
    password = case when email is not null and password is not null then Param3 end
WHERE id = Param4;

Now the question was updated and I understand that you want to perform the update if and only if both email and password parameters are not empty strings. So you actually want filtering. I would phrase this as:

UPDATE users 
SET username = Param1, email = Param2, password = Param3 
WHERE id = Param4 and Param2 <> '' and Param3 <> ''

Or if you want to separate the logic for both parameters:

UPDATE users 
SET 
    username = Param1, 
    email = case when Param2 <> '' then Param2 else email end, 
    password = case when Param3 <> '' then Param3 else password end
WHERE id = Param4;

Cid 2020-01-31 16:41:30

"your first code still empty my password in database" This is certainly because of the content of Param3

Cid 2020-01-31 16:50:38

@mafortis your question needs to be updated, adding the empty string information.

GMB 2020-01-31 17:01:10

@mafortis: I updated my answer.

mafortis 2020-01-31 17:03:50

thanks, i think you got me wrong again update if and only if both email and password, i make it separate as much as possible for you, 1- if email is not empty string, update email, 2- if password is not empty string, update password. one by one not both together,

mafortis 2020-01-31 17:04:07

yep i see your 4th logic now :) i try that :D