不为空时更新字段

问题描述

我有一条update语句，用于更新ID = xx的字段x，y和z.

I have an update statement that updates fields x, y and z where id = xx.

在表中，我有几个不同的x_created_datetime字段(用于由不同人员维护/输入的记录的不同部分).我想编写一个查询，如果为null，则将更新此字段，但如果不为null，则将其保留.

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.

所以我有:

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?

推荐答案

执行以下操作:

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

COALESCE函数选择第一个非空值.在这种情况下，它将把datestamp scan_created_date更新为相同的值(如果存在)，否则将用您替换"current_unix_timestamp"的任何值.

The COALESCE function 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.

这篇关于不为空时更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！