if“子查询返回多于 1 行”将其视为 NULL

Question

我正在尝试将 newtable 上的存储 id 与主表中的 id 同步：

UPDATE newtable t SET t.store_id = (SELECT store_id FROM maintable s 
WHERE t.state = s.state AND s.city = t.city AND t.name = s.name)

每当子查询返回多于一行时，它就会错误地显示“子查询返回多于 1 行”，但是当它返回零行时，子查询被认为是没有返回任何内容，因此 newtable 上的 store_id 保持为 NULL。这里没什么新鲜的，这就是它的工作原理。

我想知道是否可以让子查询输出与当它有多个匹配行时没有匹配项时的输出相同。

这样我就可以只同步主表上的一个匹配行的 store_id ，并在子查询中出现多个匹配行时跳过。

Answer 1

我认为您可能正在寻找 HAVING 子句来强制查询仅匹配一次：

UPDATE newtable t
SET t.store_id = (
    SELECT store_id
    FROM maintable s
    WHERE t.state = s.state
      AND s.city  = t.city
      AND t.name = s.name
    HAVING COUNT(*) = 1
)

这应该使多个匹配的行为与不匹配相同。 HAVING 子句几乎在查询过程的最后应用；如果 WHERE 中没有匹配项或多个匹配项，则 COUNT(*) = 1 将失败，内部查询将不返回任何内容，但如果恰好只有一行，则 COUNT( *) = 1 将成功，内部查询将返回该单个匹配项。

Answer 2

您可以考虑在子查询中添加LIMIT 1，以更好地实现您想要实现的目标，具体取决于您的具体需求。

否则，您应该能够通过 IF 或 CASE 发挥创意：

UPDATE newtable t SET t.store_id = (
    SELECT IF(num>1, NULL, storeid) FROM (
        SELECT COUNT(*) AS num, storeid FROM maintable s WHERE t.state=s.state AND s.city=t.city AND t.name=s.name
    )
)

未经测试，但应该可以让您达到大概水平。

Answer 3

UPDATE newtable t SET t.store_id = IFNULL((SELECT store_id FROM maintable s 
WHERE t.state = s.state AND s.city = t.city AND t.name = s.name HAVING COUNT(*) = 1), t.store_id)

IFNULL(use_this_value_if_not_null,value_if_first_isnull)