我有这个查询
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.
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;
“您的第一个代码仍然在数据库中清空了我的密码”这肯定是由于
Param3
@mafortis您的问题需要更新,添加空字符串信息。
@mafortis:我更新了答案。
谢谢,我想你让我又错了
update if and only if both email and password
,我让它分开,尽可能为你1- if email is not empty string, update email
,2- if password is not empty string, update password
。一一不一起是的,我现在看到您的第四个逻辑:)我尝试:D