How to Update a column according to count of the element in another column-CodePudding

I have an SQLite table set up like this

log_table
 
id           Street         Status      
----------   ----------     ----------
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 have multiple rows of data. At the start status table is 0 for every row. I want to update it according to the count of how many times the street value for that row appears in all of data. For example for the first row the street name is "main_street" since it appears 3 times in all of my data I want to set it to 1. My criteria for the status table is if street appears >2 it should be set to 1. If street appears <=2 I want status to be set to 0. For the 5th row the status table should be set to 0 since "2nd_street" appears <=2.

I have tried to approach it with a mix of GROUP_BY and COUNT but i couldn't come up with an answer. How should I approach this?

CodePudding user response：

Use a correlated subquery that counts the number of rows of the table that contain the same street:

UPDATE log_table AS t1
SET status = (SELECT COUNT(*) FROM log_table AS t2 WHERE t2.street = t1.street) > 2;

See the demo.