当不为空时更新字段

发布于 2024-08-13 23:36:34 字数 525 浏览 3 评论 0原文

我有一个更新语句，用于更新字段 x、y 和 z，其中 id = xx。

在表中，我有一些不同的 x_created_datetime 字段（针对由不同人员维护/输入的记录的不同部分）。我想编写一个查询，如果该字段为空，则更新该字段，但如果不为空，则保留它。

所以我所拥有的是：

UPDATE newspapers
SET scan_notes = "data",
    scan_entered_by = "some_name",
    scan_modified_date = "current_unix_timestamp"
WHERE id = X

我需要的是一种添加以下内容的方法，但仍然总是更新上面的内容：

scan_created_date = "current_unix_timestamp"
where scan_created_date is null

我希望我可以在不向数据库进行第二个事务的情况下执行此操作。关于如何实现这一目标有什么想法吗？

原文

I have an update statement that updates fields x, y and z where 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.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乱世争霸 2024-08-20 23:36:34

执行此操作：

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 函数选择第一个非空值。在这种情况下，它会将日期戳 scan_created_date 更新为相同的值（如果存在），否则它将采用您替换 "current_unix_timestamp" 的内容。

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 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.

回复收藏 0 原文

故事还在继续 2024-08-20 23:36:34

mySQL 有一个 IFNULL 函数，所以你可以这样做：

UPDATE newspapers
SET scan_notes = "data",
    scan_entered_by = "some_name",
    scan_modified_date = "current_unix_timestamp"
    scan_created_date = IFNULL( scan_created_date, "current_unix_timestamp" )
WHERE id = X

mySQL has an IFNULL function, so you could do:

UPDATE newspapers
SET scan_notes = "data",
    scan_entered_by = "some_name",
    scan_modified_date = "current_unix_timestamp"
    scan_created_date = IFNULL( scan_created_date, "current_unix_timestamp" )
WHERE id = X

回复收藏 0 原文

半岛未凉 2024-08-20 23:36:34

我认为您正在寻找的是 IF ()

UPDATE newspapers
SET scan_notes = "data",
    scan_entered_by = "some_name",
    scan_modified_date = "current_unix_timestamp",
    scan_created_date = IF(scan_created_date IS NOT NULL, "current_unix_timestamp", NULL)
WHERE id = X

I think that what you're looking for is IF()

UPDATE newspapers
SET scan_notes = "data",
    scan_entered_by = "some_name",
    scan_modified_date = "current_unix_timestamp",
    scan_created_date = IF(scan_created_date IS NOT NULL, "current_unix_timestamp", NULL)
WHERE id = X

回复收藏 0 原文

箜明 2024-08-20 23:36:34

您可以使用 COALESCE() 返回第一个非空值）：

scan_created_date = COALESCE(scan_created_date, "current_unix_timestamp")

You could use COALESCE() wich returns the first NON-NULL value):

scan_created_date = COALESCE(scan_created_date, "current_unix_timestamp")

回复收藏 0 原文

同展鸳鸯锦 2024-08-20 23:36:34

你可以这样做：

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

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

回复收藏 0 原文

岁月染过的梦 2024-08-20 23:36:34

它相当于 Oracle 的 NVL。
您可以像下面一样在使用参数的准备好的语句中使用它

UPDATE
    tbl_cccustomerinfo
SET
    customerAddress = COALESCE(?,customerAddress),
    customerName =  COALESCE(?,customerName),
    description =  COALESCE(?,description)
WHERE
    contactNumber=?

Its like equivalent to Oracle's NVL.
You can use it like below in a prepared statement using parameters

UPDATE
    tbl_cccustomerinfo
SET
    customerAddress = COALESCE(?,customerAddress),
    customerName =  COALESCE(?,customerName),
    description =  COALESCE(?,description)
WHERE
    contactNumber=?

回复收藏 0 原文

~没有更多了~