Update a column with count of the values from another column

January 2, 2023

I am creating a MySQL table with the following query.

CREATE TABLE log_table (id INTEGER, street TEXT, status INTEGER)

and filling it with the following query:

INSERT INTO log_table (`id`, `street`, `status`) VALUES
  ('1', 'main_street', '0'),
  ('2', '1st_street', '0'),
  ('3', '1st_street', '0'),
  ('4', 'main_street', '0'),
  ('5', '2nd_street', '0'),
  ('6', '1st_street', '0'),
  ('7', 'main_street', '0'),
  ('8', '2nd_street', '0');

I am trying to update the status column with the count of the street column.
For example for the first row status column should be 3 because main_street appears 3 times in street column.

I have tried the following query but it doesn’t work.

UPDATE log_table l1 SET status = 
(SELECT COUNT(*) FROM log_table l2 WHERE l2.street = l1.street);

It gives an error that says "You can’t specify target table ‘l1’ for update in FROM clause". What could be the issue?

>Solution :

You can use a join:

  UPDATE log_table l1 
     join (SELECT COUNT(*) as n, street  FROM log_table l2 group by l2.street) as l3 on l1.street=l3.street 
  SET status = l3.n

SQLFiddle