SQL(如果不为空)更新 [英] SQL if not null update

问题描述

我有这个查询

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;

---

现在，问题已更新，并且我了解您希望在电子邮件和密码参数都不为空字符串的情况下执行更新.因此，您实际上需要过滤.我将其表达为:

---

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;

这篇关于SQL(如果不为空)更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！