代码之家 › 专栏 › 技术社区 › Toto

使用“选择”更新,不使用“分组依据”进行分组

mysql-error-1093 sql mysql

Toto · 技术社区 · 15 年前

我有一张这样的表(mysql 5.0.x,myisam):

response{id, title, status, ...} (status: 1 new, 3 multi)

我想从新更新状态( status=1 (多) status=3 )在所有回答中,如果至少有20个具有相同的标题。

我有这个,但它不起作用:

UPDATE response SET status = 3 WHERE status = 1 AND title IN (
  SELECT title FROM (
   SELECT DISTINCT(r.title) FROM response r WHERE EXISTS (
     SELECT 1 FROM response spam WHERE spam.title = r.title LIMIT 20, 1)
   )
  as u)

请注意:

我做嵌套选择以避免 You can't specify target table 'response' for update in FROM clause
我不能用 GROUP BY 出于性能原因。带有解决方案的查询成本使用 LIMIT 更好(但可读性较差)。

编辑:

可以从MySQL中的更新目标中进行选择。 See solution here
问题出在所选数据上,这完全是错误的。

我找到的唯一有效的解决方案是 小组通过 以下内容:

UPDATE response SET status = 3
 WHERE status = 1 AND title IN (SELECT title 
                                  FROM (SELECT title 
                                          FROM response 
                                      GROUP BY title 
                                        HAVING COUNT(1) >= 20)

作为派生响应)

谢谢你的帮助!:)

4 回复 | 直到 13 年前

kriss 15 年前

我会写一些直截了当的东西如下

UPDATE `response`, (
  SELECT title, count(title) as count from `response`
     WHERE status = 1
     GROUP BY title
  ) AS tmp
  SET response.status = 3
  WHERE status = 1 AND response.title = tmp.title AND count >= 20;

使用group by真的很慢吗?您试图实现的解决方案看起来像是在同一个表上一次又一次地请求,如果成功的话,应该比使用group by慢得多。

Bill Karwin 15 年前

MySQL不喜欢在一个查询中更新和选择同一个表。它与锁定优先级等有关。

下面是我如何解决这个问题:

SELECT CONCAT('UPDATE response SET status = 3 ',
  'WHERE status = 1 AND title = ', QUOTE(title), ';') AS sql
FROM response
GROUP BY title
HAVING COUNT(*) >= 20;

此查询生成一系列更新语句,其中引用的标题应嵌入更新。捕获结果并将其作为SQL脚本运行。

我明白 GROUP BY 在MySQL中,经常会产生一个临时表,这可能会很昂贵。但这会破坏交易吗?您需要多久运行一次这个查询?此外,任何其他解决方案也可能需要一个临时表。

我可以想出一种不用的方法来解决这个问题 小组通过 :

CREATE TEMPORARY TABLE titlecount (c INTEGER, title VARCHAR(100) PRIMARY KEY);

INSERT INTO titlecount (c, title)
 SELECT 1, title FROM response
 ON DUPLICATE KEY UPDATE c = c+1;

UPDATE response JOIN titlecount USING (title)
SET response.status = 3
WHERE response.status = 1 AND titlecount.c >= 20;

但这也使用了一个临时表,这就是为什么您试图避免使用 小组通过 首先。

Eric Petroelje 15 年前

这是MySQL的一个有趣的特性——我想不出一个在一个语句中(group by或no group by)实现它的方法。

您可以先在临时表中选择适当的响应行,然后通过从临时表中选择进行更新。

longneck 15 年前

您必须使用临时表:

create temporary table r_update (title varchar(10));

insert r_update
select title
  from response
group
    by title
having count(*) < 20;

update response r
left outer
  join r_update ru
    on ru.title = r.title
   set status = case when ru.title is null then 3 else 1;