Mysql: Update table with count from same table

Question

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?

So you have the same url many times in the table and you want to update all its rows with the count number? And you will be updating this many rows each time new row will be added? That will be really slow... — Marki555
– Marki555, Commented Jul 8, 2015 at 9:50

CodeBird · Accepted Answer · 2015-07-08 09:24:45Z

1

You can try this query:

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

Here's a fiddle

edited Jul 8, 2015 at 9:24

answered Jul 8, 2015 at 9:11

CodeBird

3,8132 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonathan Leffler · Accepted Answer · 2017-05-24 16:29:00Z

1

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;

edited May 24, 2017 at 16:29

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

answered Jul 8, 2015 at 9:02

James Joyce Alano

7033 silver badges13 bronze badges

Collectives™ on Stack Overflow

Mysql: Update table with count from same table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related