MySQL 不为空时更新字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1860216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update Field When Not Null
提问by Cory Dee
I have an update statement that updates fields x, y and z where id = xx.
我有一个更新语句，更新字段 x、y 和 z，其中 id = xx。
In the table I have a few different x_created_datetime fields (for different portions of the record that are maintained/entered by different folks). I'd like to write a single query that will update this field if is null, but leave it alone if is not null.
在表中，我有几个不同的 x_created_datetime 字段（针对由不同人维护/输入的记录的不同部分）。我想编写一个查询，如果为空则更新此字段，但如果不为空则不理会它。
So what I have is:
所以我所拥有的是：
UPDATE newspapers
SET scan_notes = "data",
scan_entered_by = "some_name",
scan_modified_date = "current_unix_timestamp"
WHERE id = X
What I need is a way to add in the following, but still always update the above:
我需要的是一种添加以下内容的方法，但仍然始终更新上述内容：
scan_created_date = "current_unix_timestamp"
where scan_created_date is null
I'm hoping I can do this without a second transaction to the DB. Any ideas on how to accomplish this?
我希望我可以在没有对数据库进行第二次交易的情况下做到这一点。关于如何实现这一点的任何想法？
回答by cjk
Do this:
做这个：
UPDATE newspapers
SET scan_notes = "data",
scan_entered_by = "some_name",
scan_modified_date = "current_unix_timestamp",
scan_created_date = COALESCE(scan_created_date, "current_unix_timestamp")
WHERE id = X
The COALESCEfunction picks the first non-null value. In this case, it will update the datestamp scan_created_date to be the same value if it exists, else it will take whatever you replace "current_unix_timestamp"with.
该COALESCE函数选取第一个非空值。在这种情况下，它会将日期戳 scan_created_date 更新为相同的值（如果存在），否则它将采用您替换的任何内容"current_unix_timestamp"。
回答by Josh Davis
回答by Joe
回答by tloach
You can do something like this:
你可以这样做：
UPDATE newspapers a, newspapers b
SET a.scan_notes = "data",
a.scan_entered_by = "some_name",
a.scan_modified_date = "current_unix_timestamp",
b.scan_created_date = "current_unix_timestamp"
WHERE a.id = X AND b.id = X AND b.scan_created_date is not NULL
回答by AdaTheDev
You could use COALESCE() wich returns the first NON-NULL value):
您可以使用 COALESCE() 返回第一个非空值）：
scan_created_date = COALESCE(scan_created_date, "current_unix_timestamp")
回答by Chand Priyankara
Its like equivalent to Oracle's NVL. You can use it like below in a prepared statement using parameters
它相当于 Oracle 的 NVL。您可以使用参数在准备好的语句中像下面一样使用它
UPDATE
tbl_cccustomerinfo
SET
customerAddress = COALESCE(?,customerAddress),
customerName = COALESCE(?,customerName),
description = COALESCE(?,description)
WHERE
contactNumber=?
