Skip to content
Advertisement

SQL if not null update

I have this query

UPDATE users SET username = Param1,email = Param2,PASSWORD = Param3 WHERE id = Param4;

this is updating my user row, but i want to say: If email OR password is not null update them otherwise let them be as they are.

What should my query be like then?

UPDATE

By friends codes below I realized now that my form sent empty string '' and not null so I think I need to check if my password is not '' and if my email is not '' instead of if it’s not null.

About logic

I think some misunderstood me by my question,

I’d like my values of columns email and password change if i send new values trough my form,

If i didn’t fill for instance my email input in my form then my email in database doesn’t need to be change (updated).

So just update each column in case their value is not empty string.

Advertisement

Answer

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;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement