SQL(如果不为空)更新

本文介绍了SQL(如果不为空)更新的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这个查询

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;