mysql 计数更新,Mysql：使用来自同一表的计数更新表

I have a table like:

`url` varchar(255) NOT NULL,

`cnturl` int(11) DEFAULT NULL,

'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

);

But this gives me the error:

You can't specify target table 'urltable ' for update in FROM clause

I tried as well:

UPDATE urltable

INNER JOIN (

SELECT COUNT(*) as cnt, url FROM urltable

) t

ON t.url=urltable.url

SET urltable.cnturl = t.cnt;

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

I tried as well:

UPDATE urltable SET cnturl =

(SELECT t.cnt FROM

(SELECT COUNT(t.id) AS cnt, url FROM urltable) t

WHERE t.url=urltable.url)

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

I guess it should be more something like this:

UPDATE urltable

INNER JOIN (

SELECT COUNT(*) as cnt, url FROM urltable t

WHERE t.url=urltable.url;

) t

SET urltable.cnturl = t.cnt;

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

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

解决方案

Try this one:

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;