How to add a new column to a table given a query?

Question

MySQL

There is a need to count all addresses from table 1 and add a new column to table 1
Table 1

id, age, gender
1, 38, 1
2, 41, 0
3, 21, 1

table 2

id_new	Адрес	id
1, Комсомольский 1,	1
2, Комсомольский 1,	2
3, Комсомольский 1,	3
4, Петрова 2,	1
5, Петрова 2,	1
6, Ленина 3,	2
7, Ленина 3,	2

The request turned out like this:

SELECT	table1.id,
    table1.age,
    table1.gender
    COUNT(table2.id_new) AS Количество_объектов
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
GROUP BY	table1.id,
      table1.age,
      table1.gender

Now we need to insert the Number_objects column in table 1. How to do it? Without rewriting the entire table, just adding a new column.

Answer 1

ALTER TABLE followed UPDATEby a subquery.

UPDATE table1
  SET count_object = ( SELECT COUNT(*) FROM table2 WHERE table1.id = table2.id )