时间戳

首页 > 解决方案 > MySQL 使用 WHERE 的结果更新条件（已解决）

mysql - MySQL 使用 WHERE 的结果更新条件（已解决）

问题描述

如何根据结果中的一个/多个条件进行更新WHERE？例如？

UPDATE tb_a, tb_b, tb_c
SET tb_a.A = 
CASE 
  WHEN COUNT(tb_b.is_field_null IS NULL) = 0 
  THEN "OK" 
  ELSE "Nope" 
END
WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"
AND tb_a.b = tb_b.a
AND tb_c.b = tb_b.c

鉴于表格是

tb_a

a          |    b        |      A
------------------------------------------
"some"     | "thing"     | "XXX"
"some"     | "thing"     | "YYY"

tb_b

a             | c           | is_field_null 
----------------------------------------------
"thing"       | "else"      | "I have things here" 
"thing"       | "else"      | NULL
"otherThing   | "otherElse" | NULL

tb_c

b            | c           | mapper 
----------------------------------------------
"else"       | "else"      | "ZZZ" 
"else"       | "else"      | "KKK"

我无条件尝试COUNT，导致整列tb_a.A修改为“否”。这就像

tb_a

a          |    b        |      A
------------------------------------------
"some"     | "thing"     | "Nope"
"some"     | "thing"     | "Nope"

但是有了COUNT，就有了 的错误Invalid use of group function。我试过了if，例如

SET tb_a.A = (if COUNT(tb_b.is_field_null IS NULL) = 0, "Yes", "Nope"),

条件，但仍然错误。

我希望完全没有修改。有人可以帮忙吗？

来自@forpas 的回答

我发现有问题。如果 tb_b 被修改为

a             | c           | is_field_null 
----------------------------------------------
"thing"       | "else"      | "I have things here" 
"thing"       | "else"      | "I have things here" 
"otherThing   | "otherElse" | NULL

那么查询后的tb_a还是

a          |    b        |      A
------------------------------------------
"some"     | "thing"     | "Nope"
"some"     | "thing"     | "Nope"

是行CROSS JOIN (SELECT SUM(is_field_null IS NULL) counter FROM tb_b) t还是WHEN NOT EXISTS(SELECT 1 FROM tb_b WHERE is_field_null IS NULL)不对WHEREandON子句返回的结果进行检查？它从原始 tb_b 本身执行检查？

来自的第三个查询forpas正在工作。

在这种情况下，我有些不太明白。从forpas的前 2 个查询中，如果我将它们从 更改UPDATE为SELECT，它们会正确选择前 2 行tb_b。但在该UPDATE模式下，既不

CROSS JOIN (SELECT SUM(is_field_null IS NULL) counter FROM tb_b) t

或者

WHEN NOT EXISTS(SELECT 1 FROM tb_b WHERE is_field_null IS NULL)

WHERE使用和的条件执行ON。有没有人可以清理我？

更新

据我所知，这里问题的当前紧凑形式是

UPDATE tb_a.A
CROSS JOIN (SELECT SUM(is_field_null IS NULL) counter 
   FROM tb_a
    INNER JOIN tb_b ON tb_a.b = tb_b.a
    INNER JOIN tb_c ON tb_c.b = tb_b.c
    WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"
) t
SET tb_a.A = CASE
  WHEN t.counter = 0 THEN "Yes"
  ELSE "Nope"
END
WHERE tb_a.a = "some"

请参阅@forpas 的完整解决方案答案以及NOT EXISTS.

标签： mysqlconditional-statementswhere-clause

解决方案

使用正确的连接以及CROSS JOIN返回如下结果的查询COUNT(tb_b.is_field_null IS NULL)：

UPDATE tb_a
INNER JOIN tb_b ON tb_a.b = tb_b.a
INNER JOIN tb_c ON tb_c.b = tb_b.c
CROSS JOIN (SELECT SUM(is_field_null IS NULL) counter FROM tb_b) t
SET tb_a.A = CASE 
  WHEN t.counter = 0 THEN "OK" 
  ELSE "Nope" 
END
WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"

或与NOT EXISTS：

UPDATE tb_a
INNER JOIN tb_b ON tb_a.b = tb_b.a
INNER JOIN tb_c ON tb_c.b = tb_b.c
SET tb_a.A = CASE 
  WHEN NOT EXISTS(SELECT 1 FROM tb_b WHERE is_field_null IS NULL) THEN "OK" 
  ELSE "Nope" 
END
WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"

编辑：

UPDATE tb_a
INNER JOIN tb_b ON tb_a.b = tb_b.a
INNER JOIN tb_c ON tb_c.b = tb_b.c
CROSS JOIN (
  SELECT SUM(is_field_null IS NULL) counter 
  FROM tb_a
  INNER JOIN tb_b ON tb_a.b = tb_b.a
  INNER JOIN tb_c ON tb_c.b = tb_b.c
  WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"
) t 
SET tb_a.A = CASE 
  WHEN t.counter = 0 THEN "OK" 
  ELSE "Nope" 
END
WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"

推荐阅读