Mysql: Update table with count from same table -

i have table like:

`url` varchar(255) not null, `cnturl` int(11) default null,

'url' contains urls. in 'cnturl' want store how many times same url inside table (yes, it's redundant, i'm doing speed reasons).

so update these values need like:

update urltable  set cnturl=(     select count(t.id) urltable t t.url=urltable.url  );

but gives me error: can't specify target table 'urltable ' update in clause

i tried well:

update urltable    inner join (     select count(*) cnt, url urltable   ) t    on t.url=urltable.url  set urltable.cnturl = t.cnt;

but gives wrong results (some counts null while others count of records).

i tried well:

update urltable set cnturl =   (select t.cnt        (select count(t.id) cnt, url urltable) t     t.url=urltable.url)

but gives same wrong results (some counts null while others count of records).

i guess should more this:

update urltable    inner join (     select count(*) cnt, url urltable t   t.url=urltable.url;   ) t    set urltable.cnturl = t.cnt;

but ends with: unknown column 'urltable.url' in 'where clause'.

i couldn't find other solutions this. other ideas?

you can try query:

update urltable set cnturl =  (select count(*)      (select * urltable) t  t.url=urltable.url);

here's fiddle

JVParth