Mysql:用同一表中的计数更新表

问题描述

我有一张桌子，像这样:

I have a table like:

`url` varchar(255) NOT NULL,
`cnturl` int(11) DEFAULT NULL,

'url'包含一些URL.在"cnturl"中，我想存储同一URL在表中的次数(是的，这是多余的，出于速度原因，我这样做).

'url' contains some urls. In 'cnturl' I want to store how many times the same url is inside the table (yes, it's redundant, I'm doing this for speed reasons).

所以要更新这些值，我需要像这样的东西:

So to update these values I need something like:

UPDATE urltable
SET cnturl=(
    SELECT COUNT(t.id) FROM urltable AS t WHERE t.url=urltable.url
 );

但是这给了我错误:您无法在FROM子句中指定目标表"urltable"进行更新

But this gives me the error:You can't specify target table 'urltable ' for update in FROM clause

我也尝试过:

UPDATE urltable
  INNER JOIN (
  SELECT COUNT(*) as cnt, url FROM urltable
  ) t
  ON t.url=urltable.url
SET urltable.cnturl = t.cnt;

但这会给出错误的结果(某些计数为NULL，而其他计数为所有记录的计数).

But this gives just wrong results (some counts are NULL while others are the count of all records).

我也尝试过:

UPDATE urltable SET cnturl =
  (SELECT t.cnt FROM
      (SELECT COUNT(t.id) AS cnt, url FROM urltable) t
   WHERE t.url=urltable.url)

但这会产生相同的错误结果(某些计数为NULL，而其他计数为所有记录的计数).

But this gives the same wrong results (some counts are NULL while others are the count of all records).

我想应该更像这样:

UPDATE urltable
  INNER JOIN (
  SELECT COUNT(*) as cnt, url FROM urltable t
  WHERE t.url=urltable.url;
  ) t
  SET urltable.cnturl = t.cnt;

但是最终结果是:"where子句"中的未知列"urltable.url".

But this ends up with: Unknown column 'urltable.url' in 'where clause'.

我找不到其他解决方案.还有其他想法吗?

I couldn't find any other solutions for this. Any other ideas?

推荐答案

尝试以下方法:

UPDATE urltable
   INNER JOIN (
   SELECT IFNULL(COUNT(url),0) as cnt, url FROM urltable
   GROUP BY url
   ) t
   ON t.url=urltable.url
SET urltable.cnturl = t.cnt;

这篇关于Mysql:用同一表中的计数更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！