温馨提示:本文翻译自stackoverflow.com,查看原文请点击:mysql - SQL if not null update

mysql - SQL(如果不为空)更新

发布于 2020-03-27 15:55:38

我有这个查询

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

这正在更新我的用户行,但我想说:如果电子邮件或密码不为null,请更新它们,否则将它们保持原样。

那我的查询应该是什么样的?

更新

通过下面的朋友代码,我现在意识到我的表单发送的是空字符串''而不是空字符串null所以我认为我需要检查密码是否不是密码''以及电子邮件的密码是否''不是null,而不是密码是否为null。

关于逻辑

我想我的问题有些误解了我,

我希望列的值能够更改,如果我通过表单发送新值email,则可以password更改,

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.

查看更多

查看更多

提问者
mafortis
被浏览
14
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 <> ''

或者,如果您想将两个参数的逻辑分开:

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;

发布
问题

分享
好友

手机
浏览

扫码手机浏览