如果值不存在，则 Mysql 更新

Question

我正在尝试编写非常奇怪的查询，并且我遇到了 if 语句的问题，所以我有 2 个字段 deleted_1 和 deleted_2 在我想要更新的表中，所以我认为它应该是这样的:

Update `messages`
  if(`deleted_1` == null) SET `deleted_1` = 1
  else
  if(`deleted_2` == null) SET `deleted_2 = 1
  ---and here goes some simple WHERE statements

但这不起作用所以基本上我想检查 a 字段是否为空然后更新它如果不检查 b 字段如果它是空更新 b 字段现在我不确定是否可以用更简单的方式来做，我在这里完全迷路了:)

Answer 1

UPDATE MyTable
SET deleted_2 = CASE WHEN ISNULL(deleted_1, '') == '' OR ISNULL(deleted_2, '') != '' THEN deleted_2 ELSE 1 END,
    deleted_1 = CASE WHEN ISNULL(deleted_1, '') != '' THEN deleted_1 ELSE 1 END

这是在说:

deleted_1: will be update if (deleted_1 == null)
deleted_2: will be updated if (deleted_1 != null && deleted_2 == null), 
           This is to match your if else condition