SQL if not null 更新

2022-01-17 00:00:00 sql-update sql mysql

我有这个问题

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?

通过下面的朋友代码，我现在意识到我的表单发送的是空字符串 '' 而不是 null 所以我想我需要检查我的密码是否不是 '' 并且如果我的电子邮件不是 '' 而不是如果它不为空.

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.

我认为有些人误解了我的问题，

I think some misunderstood me by my question,

如果我通过表单发送新值，我希望 email 和 password 列的值发生变化，

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.

推荐答案

如果电子邮件或密码不为空，请更新它们，否则让它们保持原样.

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

您可以为此使用 case 表达式.我认为你想要的逻辑是:

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;

或者如果您想更新电子邮件和密码，如果 两者 都不是 null 则:

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;

<小时>

现在问题已更新，我了解当且仅当电子邮件和密码 parameters 都不是空字符串时，您才希望执行更新.所以你实际上想要过滤.我将其表述为:

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;

相关文章